QueryBuilder: Sum values of a pivot table

Question

I'm new on Laravel & Eloquent. I have Users, Products and Votes tables on my DB. Users can vote (0 to 5) on products, so it is a "many to many" relationship, with the Votes table acting like a pivot:

Users: id, name, email, password
Products: id, name, model, brand
Votes: user_id, product_id, vote

I mapped this schema like this:

// User model:
public function product_votes()
{
    return $this->belongsToMany('App\Product', 'votes')->withPivot('vote');
}

// Product model:
public function product_votes()
{
    return $this->belongsToMany('App\User', 'votes')->withPivot('vote');
}

So John can vote 5 on product X and 0 on product Y. Bob can vote 2 on product X, 3 on product Y, and so on...

I'm trying to query all products, with the sum of votes of each one of them. Something like:

SELECT p.*, (SUM(v.vote) / COUNT(*)) as votes FROM products p INNER JOIN votes v on v.product_id = p.id GROUP BY p.id

How can I do that with QueryBuilder? My mapping is right?

jedrzej.kurylo jedrzej.kurylo · Accepted Answer · 2016-01-08T07:31:29

The following will do the trick:

Product::select('products.*', DB::raw('SUM(vote)/COUNT(*) as votes'))
->join('votes', 'votes.product_id', '=', 'products.id')
->groupBy('products.id')
->get();

You can see the query that will be run by calling toSql() instead of get().

QueryBuilder: Sum values of a pivot table

1 Answers