Laravel - unable to join table in query

Question

I don't know why in bellow query I don't have data from joining table:

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();

I'm getting proper results but I without data from "tags" table.

My result:

[{"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}]

What is missing in results is "tags_name".

My question is how to retrieve data from joining table.

Regards.

Updated query:

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();

Error in query:

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)

Tables structure: tags_ref: id, post_id, tag_id tags: id, name

Updated query:

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();

Error:

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)

Share your results and expected ? You are using groupBy so you will get merged with groupBy — Niklesh Raut
– Niklesh Raut, Commented May 7, 2018 at 16:24
Add column name tag_name in groupBy and also change tags.* to tags.tag_name in select — Niklesh Raut
– Niklesh Raut, Commented May 7, 2018 at 17:29

Niklesh Raut · Accepted Answer · 2018-05-07 17:29:59Z

1

To get tags column you need add those columns

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

edited May 7, 2018 at 17:29

answered May 7, 2018 at 16:21

Niklesh Raut

34.9k17 gold badges82 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jonas Staudenmeir Over a year ago

Just tags.name?

Klick · Accepted Answer · 2018-05-08 16:15:10Z

0

Finally I resolved this problem. This is a proper query:

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

answered May 8, 2018 at 16:15

Klick

1,6832 gold badges26 silver badges54 bronze badges

Collectives™ on Stack Overflow

Laravel - unable to join table in query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related