I am using Laravel 5.4's Query Builder to perform a series of leftJoins on three tables. Here are my tables:
items
| id | type | title | visibility | status | created_at |
|----|------|---------------------|------------|--------|---------------------|
| 1 | 1 | This is a Title | 1 | 1 | 2017-06-20 06:39:20 |
| 2 | 1 | Here's Another Item | 1 | 1 | 2017-06-24 18:12:13 |
| 3 | 1 | A Third Item | 1 | 1 | 2017-06-26 10:10:34 |
count_loves
| id | items_id | user_id |
|----|----------|---------|
| 1 | 1 | 2 |
| 2 | 1 | 57 |
| 3 | 1 | 18 |
count_downloads
| id | items_id | user_id |
|----|----------|---------|
| 1 | 1 | 879 |
| 2 | 1 | 323 |
And here is the code I am running in Laravel:
$items_output = DB::table('items')
->leftJoin('count_loves', 'items.id', '=', 'count_loves.items_id')
->leftJoin('count_downloads', 'items.id', '=', 'count_downloads.items_id')
->where('items.visibility', '=', '1')
->where('items.status', '=', '1')
->orderBy('items.created_at', 'desc')
->select('items.*', DB::raw('count(count_loves.id) as loveCount'), DB::raw('count(count_downloads.id) as downloadCount'))
->groupBy('items.id')
->get();
When I return the results for this query, I am getting the following counts:
count_loves: 6
count_downloads: 6
As you can see, the actual count values * should * be:
count_loves: 3
count_downloads: 2
If I add another entry to the * count_loves * table, as an example, the totals move to 8. If I add another entry to the * count_downloads * table after that, the totals jump to 12. So, the two counts are multiplying together.
If I die and dump the query, here's what I get:
"query" => "select 'items'.*, count(count_loves.id) as loveCount, count(count_downloads.id) as downloadCount from 'items' left join 'count_loves' on 'items'.'id' = 'count_loves'.'items_id' left join 'count_downloads' on 'items'.'id' = 'count_downloads'.'items_id' where 'items'.'visibility' = ? and 'items'.'status' = ? group by 'items'.'id' order by 'items'.'created_at' desc"
How do I perform multiple leftJoins using Query Builder and count on several tables to return the proper sums?