socm_ socm_ - 6 months ago 15
PHP Question

Two joins and two count getting laravel

I have 3 tables in example. users, grammar_learned, words_learned

First:

CREATE TABLE grammar_learned
(
id INT(10) UNSIGNED PRIMARY KEY NOT NULL,
grammar_id INT(11) NOT NULL,
user_id INT(11) NOT NULL,
);

CREATE TABLE words_learned
(
id INT(10) UNSIGNED PRIMARY KEY NOT NULL,
word_id INT(11) NOT NULL,
user_id INT(11) NOT NULL,
);

CREATE TABLE users
(
id INT(10) UNSIGNED PRIMARY KEY NOT NULL,
name VARCHAR(255) NOT NULL,
);


And my query:

$users = User::leftJoin('words_learned', 'users.id', '=', 'words_learned.user_id')
->leftJoin('grammar_learned', 'users.id', '=', 'grammar_learned.user_id')
->selectRaw('users.*, count(words_learned.id) as learned_count, count(grammar_learned.id) as grammar_count')
->groupBy('users.id')->orderBy("learned_count", "desc")->get();


I have 1 row in my table grammar_learned and 3 row in words_learned table, but query returns 3 row (count) for words_learned - properly and 3 rows for grammar_learned (must returns 1). I don't understand why, please help

Shortly:
I want this result:

0 => array:4 [▼
"id" => 1
"name" => "username"
"learned_count" => 3
"grammar_count" => 1
]


But I get

0 => array:4 [▼
"id" => 1
"name" => "username"
"learned_count" => 3
"grammar_count" => 3
]

Answer

Here, try the following. I removed your joins and used subqueries instead...

$selectString = 'users.*, (select count(1) from words_learned wl where wl.user_id = users.id) as learned_count, ';
$selectString = $selectString . '(select count(1) from grammar_learned gl where gl.user_id = users.id) as grammar_count ';
$users = User::selectRaw($selectString)->groupBy('users.id')->orderBy("learned_count", "desc")->get();

Here is what it comes out to as straight SQL. You had the following:

select u.*, count(wl.id) as word_cnt, 
       count(gl.if) as grammar_cnt
from Users u
join words_learned wl
on wl.user_id = u.id
join grammar_learned gl
on gl.user_id = u.id
group by u.id
order by word_cnt desc;

Which I basically changed to this:

select u.*, 
      (select count(1) from words_learned wl where wl.user_id = u.id) as word_count,
      (select count(1) from grammar_learned gl where gl.user_id = u.id) as grammar_count
from users u
group by u.id
order by word_count desc;