Failed to get result using laravel query builder to join two table

Question

I have two tables 'users' and 'channel'

Table: users

id  name        channel
1   user1       1,2,3
2   user2       2,3
3   user3       2

Table: channel

id   channel_name
1    IT
2    CS
3    EC

I need result as

name     channel_name
user1    IT,CS,EC
user2    CS,EC
user3    CS

Using laravel query builder how I write the query?

I tried below, but I got channel_name as NULL.

try 1

$UserChannelList = Users::select('users.name as username', DB::raw("(GROUP_CONCAT(channels.channel_name SEPARATOR ',')) as 'channel_name'"))
        ->leftjoin('channels', function ($join) {
          $join->whereRaw("FIND_IN_SET('channels.id', 'users.channel')");
        })
        ->groupBy('users.name')
        ->orderBy('users.name', 'ASC')
        ->get();

try 2

$UserChannelList = Users::select('users.name as username', DB::raw("(GROUP_CONCAT(channel.channel_name SEPARATOR ',')) as 'channel_name'"))
        ->leftjoin('channel', function ($join) {
          $join->on(DB::raw("CONCAT(',', 'users.channel', ',')"), 'like', DB::raw("CONCAT(',','channel.id',',')"));
        })
        ->groupBy('users.name')
        ->orderBy('users.name', 'ASC')
        ->get();

You never store data this way. There are no relationships defined between models. — mighty_dev
– mighty_dev, Commented Jan 16, 2020 at 10:31

Dilip Hirapara · Accepted Answer · 2020-01-16 10:37:13Z

1

Try with this query.

\DB::table("users")
        ->select("users.*",\DB::raw("GROUP_CONCAT(channels.channel_name) as channel_name"))
        ->leftjoin("channels",\DB::raw("FIND_IN_SET(channels.id,users.channel)"),">",\DB::raw("'0'"))
        ->get();

Please check my answer

answered Jan 16, 2020 at 10:37

Dilip Hirapara

15.4k3 gold badges34 silver badges56 bronze badges

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

Comments

Mohammad Gayashuddin · Accepted Answer · 2020-01-16 11:40:56Z

0

Here is your solution please check

    $UserChannelList = DB::table('users')
                        ->select('users.name', DB::raw("(GROUP_CONCAT(channels.channel_name)) as 'channel_name'"))
                        ->rightJoin('channels', function($join){
                            $join->whereRaw('FIND_IN_SET(channels.id, users.channel)');
                        })
                        ->groupBy('users.name')
                        ->orderBy('users.name', 'ASC')
                        ->get();

edited Jan 16, 2020 at 11:40

answered Jan 16, 2020 at 11:31

Mohammad Gayashuddin

214 bronze badges

Collectives™ on Stack Overflow

Failed to get result using laravel query builder to join two table

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related