示例中有3个表。users，grammar_learned，words_learned
第一：
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,
);
我的问题是：
$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();
我的表grammar_learned中有1行，words_learned表中有3行，但query为words_learned正确返回3行（count），为grammar_learned 很快：我想要这个结果： 但我有 0 => array:4 [▼
"id" => 1
"name" => "username"
"learned_count" => 3
"grammar_count" => 1
]
0 => array:4 [▼
"id" => 1
"name" => "username"
"learned_count" => 3
"grammar_count" => 3
]
在这里，尝试以下操作。我删除了你的联接，改为使用子查询。。。
$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();
以下是它的直接SQL。您有以下内容：
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;
我基本上改成了这个：
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;