首頁猿問使用 laravel...

使用 laravel 查詢生成器連接兩個(gè)表無法獲得結(jié)果

PHP

largeQ 2022-07-22 16:05:58

我有兩個(gè)表'用戶'和'頻道'表：用戶id name channel1 user1 1,2,32 user2 2,33 user3 2表：渠道id channel_name1 IT2 CS3 EC我需要結(jié)果name channel_nameuser1 IT,CS,ECuser2 CS,ECuser3 CS使用 laravel 查詢生成器如何編寫查詢？我在下面嘗試過，但我將channel_name 設(shè)置為NULL。試試 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();試試 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();

查看完整描述

2 回答

紅顏莎娜

TA貢獻(xiàn)1842條經(jīng)驗(yàn) 獲得超13個(gè)贊

試試這個(gè)查詢。

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

請(qǐng)檢查我的回答

反對(duì) 回復(fù) 2022-07-22

哈士奇WWW

TA貢獻(xiàn)1799條經(jīng)驗(yàn) 獲得超6個(gè)贊

這是您的解決方案，請(qǐng)檢查

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

反對(duì) 回復(fù) 2022-07-22