Eloquent, Query Builder or other way?

Level 51

You could do

Clientes::with('clientes_contactos')
    ->addSelect(DB::raw('count(*) as total'))
    ->groupBy('clienteid')
    ->get();

I've not tested but should work (you might need to tinker a bit).

Level 1

Thank you @D9705996 for your anwser!

I am trying to adapt this problem as close as possible to the manual destribed in docs: https://laravel.com/docs/5.4/eloquent-relationships#counting-related-models

Level 67

Do you want to actually get the related items, or only a count of the related items?

If it's just the count without the contents

$clients = Clientes::withCount('clientes_contactos')->get();

then in the view

@foreach ($clients as $client)
  Client Name: {{ $client->name }}
  Contact Count: {{ $client->clientes_contactos_count }}
@endforeach

D9705996

Level 51

I don't think withCount will help you as it eager loads the related table count so wouldn't take into consideration the group by.

Level 1

Ty @Cronix ! The count number will sufice. But when I use the $clients = Clientes::withCount('clientes_contactos')->get(); it gives me the error: Call to undefined method App\clientes::clientes_contactos(). Do I have to define this on the Clientes model ?

Level 1

@D9705996 Thank you for that input!

https://laravel.com/docs/5.4/eloquent-relationships

Level 67

Do I have to define this on the Clientes model ?

Yes, you'd need to create a clientes_contactos relationships in the Clientes model. It sounds like it would be a one-to-many (a client can have many contacts) https://laravel.com/docs/5.4/eloquent-relationships#one-to-many

That is unless Clients can have contacts in common, then it would be a many-to-many.

1 like

Level 1

@cronix Thank you! I guess that is my struggle. The "creation of relationships" will give it a go and post a feedback asap. This is a new concept for me.

Best Answer

Level 67

It will tremendously simplify your life, and code.

I'd highly recommend watching

Laravel in general: https://laracasts.com/series/laravel-from-scratch-2018
Relationships: https://laracasts.com/series/eloquent-relationships

Level 1

@Cronix

It is working did this (for future reference or in case another person need this info). In the Client model defined relation to contacts and other away arround also hasMany - belongsTo. Called within controller with GET all good and working. Thank you!

Now for the next level.... what if, we need to call the count of other relation still lets say... Number of contacts (already done) and number of orders from orders table. (assuming the relationship is already done)

Will watch the videos thanks, started with laravel 3 days ago!

Level 67

what if, we need to call the count of other relation still lets say... Number of contacts (already done) and number of orders from orders table. (assuming the relationship is already done)

The exact same way, except a separate withCount() method.

$clients = Clientes::withCount('clientes_contactos')->withCount('orders')->get();

@foreach ($clients as $client)
  Client Name: {{ $client->name }}
  Contact Count: {{ $client->clientes_contactos_count }}
  Order Count: {{ $client->orders_count }}
@endforeach

assuming the name of the relationship is orders and the relationship is defined in the Clientes model.

If you have more questions, please ask in a new thread. It's best to have 1 question/answer per thread for others looking for similar answers.

1 like