CODEWITHSUNDEEP
laravel-5eloquent
Steevie
Steevie

Reputation: 680

How to use a count-aggregate function directly on a pivot table?

I am looking for the equivalent in Eloquent/ Laravel to the SQL statement

SELECT label_id, COUNT(*) FROM label_picture WHERE picture_id IN (7,12) GROUP BY label_id

and don't know how to do this. Specifically I have the following many-to-many relation defined in Laravel:

// Picture model
public function labels() { return $this->belongsToMany('App\Label'); }

// Label model
public function pictures() { return $this->belongsToMany('App\Picture'); }

If I want to figure out how often each label ist used, I can simply walk through all labels and call $label->pictures->count() for each label. But I don't want to do this for the whole set of pictures, but only for a selected list of pictures (e. g.: ids 7 and 12 in the SQL example in the beginning).

My pivot table looks like the following:

label_picture:

label_id: 1, picture_id:  1
label_id: 1, picture_id:  2
label_id: 2, picture_id:  7
label_id: 2, picture_id:  3
label_id: 1, picture_id: 12
label_id: 3, picture_id:  7
label_id: 2, picture_id:  2
label_id: 1, picture_id:  7
label_id: 3, picture_id: 12

and the SQL statement

SELECT label_id, COUNT(*) FROM label_picture WHERE picture_id IN (7,12) GROUP BY label_id

would result in

label_id    COUNT(*)
1           2
2           1
3           2

How do I do this in Eloquent/ Laravel?

Upvotes: 0

Views: 381

Answers (1)

jaysingkar
jaysingkar

Reputation: 4435

I'm not sure about any eloquent method of doing this, but you can use QueryBuilder to do this like below:

$picture_ids = [7,12]; //replace this with your way to get picture ids
$labels = DB::table('label_picture')
            ->selectRaw('label_id,count(*) as total')
            ->whereIn('picture_id',$picture_ids)
            ->groupBy('label_id')
            ->get();

Upvotes: 1

Related Questions