CODEWITHSUNDEEP
laraveleloquentlaravel-8eloquent-relationship
AKT
AKT

Reputation: 54

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

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.

Upvotes: 1

Views: 38

Answers (1)

Vinicius de Santana
Vinicius de Santana

Reputation: 36

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

Upvotes: 1

Related Questions