php - 两个连接和两个计数得到 laravel

Question

示例中有 3 个表。用户，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 行，但是查询为 words_learned 返回 3 行(计数)- 正确地为 grammar_learned 返回 3 行(必须返回 1)。不明白为什么，求助

很快:我想要这个结果:

 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
  ]

Answer 1

在这里，尝试以下操作。我删除了您的连接并改为使用子查询...

$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;