How do I get both a COUNT() and SUM() LEFT JOIN in Laravel Eloquent?-CodePudding

I'm pretty new to joins so excuse me as I get my head round it! I'm trying to join 2 tables onto a links table.

One table is clicks. I want the SUM of any data in the clicks.clicks column where link_id matches. (ie 2 columns, One with 1 in the column and a second with 4 in the clicks column both for link_id=1 would return 5)

And the second table is suggestions which I want the COUNT of any occurrence of link_id to be displayed (ie 4 rows where link_id=1 would return 4)

I'm using eloquent with 2 left joins for this and have managed to get both working independently, however when I put both together, clicks_sum which is the name of my SUM join goes wildly high (Seeming as if the suggestion_count is interfering with what's going on there)

Here's my code so far for what I'm trying to achieve. I'm loading Link ID 2872719 just to test with. It should return 585 for suggestion_count and 4 for clicks_sum but I am getting totally different results than expected

use App\Models\Link;
use App\Models\Suggestion;

return Link::select(
  'links.id',
  DB::raw('SUM(clicks.clicks) AS click_sum'), 
  DB::raw('COUNT(suggestions.link_id) AS suggestion_count'),    
)
->leftJoin('clicks', 'clicks.link_id', '=', 'links.id')
->leftJoin('suggestions', 'suggestions.link_id', '=', 'links.id')

->where('links.id', 2872719)
->first();

Returned is the following:

App\Models\Link {#1278
  id: 2872719,
  click_sum: "2340", // Should be 4
  suggestion_count: 585, // The presence of this join appears to affect click_sum
}

Any ideas on where I am going wrong?

All my best!

CodePudding user response：

Rather than having two leftJoin instances in your eloquent query you need to combine the two joins together into one leftJoin.

return Link::select(
  'links.id',
  // get the count of suggestions for each link
  DB::raw('(SELECT COUNT(*) FROM suggestions WHERE suggestions.link_id = links.id) AS suggestion_count'),
  DB::raw('SUM(clicks.clicks) AS click_sum'), 
)
->leftJoin('clicks', 'clicks.link_id', '=', 'links.id', 'suggestions.link_id', '=', 'links.id')

->where('links.id', 2872719)
->groupBy('links.id')
->first();

This should give you the correct result as below:

 #original: array:3 [▼
    "id" => 2872719
    "suggestion_count" => 586
    "click_sum" => "4"
  ]

When performing two leftJoin as you were previously, the click_sum was being multiplied by the total count of suggestions leading to the inflated number being returned.