mysql - Laravel - 无法在查询中连接表

Question

我不知道为什么在下面的查询中我没有来自连接表的数据:

DB::table('tags_ref')
        ->join('tags', 'tags_ref.tag_id', '=', 'tags.id')
        ->select(DB::raw('count(tag_id) as repetition, tag_id'))
        ->groupBy('tag_id')
        ->orderBy('repetition', 'desc')
        ->get();

我得到了正确的结果，但没有“标签”表中的数据。

我的结果:

[{"repetition":6,"tag_id":1},{"repetition":5,"tag_id":14},{"repetition":4,"tag_id":42},{"repetition":4,"tag_id":32},{"repetition":4,"tag_id":103},{"repetition":4,"tag_id":4},{"repetition":3,"tag_id":13},{"repetition":3,"tag_id":83},{"repetition":3,"tag_id":15},{"repetition":3,"tag_id":61},{"repetition":3,"tag_id":105},{"repetition":3,"tag_id":60}]

结果中缺少的是“tags_name”。

我的问题是如何从连接表中检索数据。

问候。

更新的查询:

DB::table('tags_ref')
        ->join('tags', 'tags_ref.tag_id', '=', 'tags.id')
        ->select(DB::raw('count(tag_id) as repetition'), 'tags.*')
        ->groupBy('tag_id')
        ->orderBy('repetition', 'desc')
        ->get();

查询错误:

SQLSTATE[42000]: Syntax error or access violation: 1055 'farmazon_lar.tags.id' isn't in GROUP BY (SQL: select count(tag_id) as repetition, `tags`.* from `tags_ref` inner join `tags` on `tags_ref`.`tag_id` = `tags`.`id` group by `tag_id` order by `repetition` desc)

表结构:标签引用:id、post_id、tag_id标签:id、名称

更新的查询:

DB::table('tags_ref')
        ->join('tags', 'tags_ref.tag_id', '=', 'tags.id')
        ->select(DB::raw('count(tag_id) as repetition'), 'tags.tag_name')
        ->groupBy('tag_id, tag_name')
        ->orderBy('repetition', 'desc')
        ->get();

错误:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'tags.tag_name' in 'field list' (SQL: select count(tag_id) as repetition, `tags`.`tag_name` from `tags_ref` inner join `tags` on `tags_ref`.`tag_id` = `tags`.`id` group by `tag_id,tag_name` order by `repetition` desc)

Answer 1

要获取标签列，您需要添加这些列

  ->select(DB::raw('count(tag_id) as repetition'), 'tags.tag_name')