Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

sheldonscott's avatar

Multiple leftJoins using Laravel's Query Builder producing incorrect counts

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?

0 likes
2 replies
mikefolsom's avatar

Are you sure you want left joins? Those will return all items from the first table, along with matched records from the joined table(s). I think a standard (inner) join may be what you're looking for...

PS try putting your data structure inside a code block. It is nearly impossible to read as-is. Thanks!

sheldonscott's avatar

Trying to table using the Git-flavoured coding, but it's having a grand time messing things up for me! lol

I'll get it resolved shortly.

As for the left joins, I want to ensure that the item is always returned, regardless of if it has been 'liked' or 'downloaded.'

Please or to participate in this conversation.