Laravel Relationships | Joins | Eloquents - Looking for a better way to write this Query -

Question

Lets suppose, I have two tables which are customers and leads.

Car table has these columns id, name, phone, source while leads table has id, customer_id (FK) and other columns.

I want to fetch count of leads against each source. Here is my query and it works just fine!

Customer::leftJoin('leads', 'leads.customer_id', '=', 'customers.id')
    ->select('customers.source', DB::raw('count(leads.id) as source_leads_count'))
    ->whereBetween('customers.created_at', [$start_date, $end_date])
    ->groupBy('customers.source')
    ->orderBy('source_leads_count', 'DESC')
    ->get()->toArray();

My above query works fine and it returns me an array.

[
    {
        "source": "facebook",
        "source_leads_count": 1300
    },
    {
        "source": "google",
        "source_leads_count": 600,
    },
]

I am looking forward to do this query with Laravel eloquent relationships if it is possible. I have tried some relationships but none of them work.

Seems like you only need one relationship. Show us what you've tried (even if it doesn't work) and any errors that show. — geertjanknapen, Jun 09 '22 at 12:12

score 1 · Accepted Answer · answered Jun 09 '22 at 13:27

try this:

$customer= Customer::withCount('leads')->get();

if you need a more complex query it will look like this:

use Illuminate\Database\Eloquent\Builder;

$customer= Customer::withCount(['leads' => function (Builder $query) {
    $query->orderBy('name');
}])->get();

reference: https://laravel.com/docs/9.x/eloquent-relationships#counting-related-models

Laravel Relationships | Joins | Eloquents - Looking for a better way to write this Query -

1 Answers1