mysql - 如何在 Laravel 模型类中实现 GROUP_CONCAT？

Question

我正在使用 GROUP_CONCAT 函数为 Laravel 中选定的专业人员获取多个照片图像路径。代码位于模型类中。代码段如下:

$professionals = DB::table('member_professional')
    ->select("member_professional.*",
        DB::raw("(GROUP_CONCAT(DISTINCT photos.picture_path SEPARATOR ',')) as 'photos'"))
    ->join('member','member_professional.member_id_fk','=','member.member_id')
    ->join('photos','photos.member_fk','=','member.member_id')
    ->leftjoin('locations','member.city','=','locations.location_id');

        if ($professionals_type != '') {
                     $professionals = $professionals->where('member_professional.biz_category','=',$professionals_type);
        }

        if ($location != '') {
                     $professionals = $professionals->where('member.city','=',$location);
        }

    $professionals = $professionals->where('member.member_type','=',2); 
    $professionals = $professionals->groupBy('member_professional.member_id_fk')
    ->paginate(20);

当我在临时服务器上运行它时:http://dev.interioradditions.pk/allProfessionals它抛出错误:

SQLSTATE[42000]: Syntax error or access violation: 1055 'interior_dev_db.member_professional.biz_name' isn't in GROUP BY (SQL: select member_professional.*, (GROUP_CONCAT(DISTINCT photos.picture_path SEPARATOR ',')) as 'photos' from member_professional inner join member on member_professional.member_id_fk = member.member_id inner join photos on photos.member_fk = member.member_id left join locations on member.city = locations.location_id where member.member_type = 2 group by member_professional.member_id_fk limit 20 offset 0)

然而，当我从上述语句中提取构建的 SQL 查询并通过 PHPMyAdmin 执行时，它运行良好并给出了预期的结果。请帮助了解模型查询中的问题。

谢谢!

Answer 1

我更喜欢通过定义模型和它们之间的关系来了解 Laravel 的方法

class MemberProfessional extends Model
{  
    public function member()
    {
        return $this->hasMany('App\Member', 'member_id', 'member_id_fk');
    }
}

class Member extends Model
{
    public function photos()
    {
        return $this->hasMany('App\Photos', 'member_fk', 'member_id');
    }    
    public function locations()
    {
        return $this->hasMany('App\Locations', 'location_id', 'city');
    }   
    public function member_professional()
    {
        return $this->belongsTo('App\MemberProfessional', 'member_id_fk');
    }   
}

class Photos extends Model{
//...
}

class Locations extends Model{
//...
}

然后你可以编写简单的 Eloquent 查询

$professionals = MemberProfessional::with('member.photos');

if ($professionals_type != '') {
    $professionals = $professionals->where('biz_category','=',$professionals_type);
}

if ($location != '') {
    $professionals->whereHas('member', function ($query) use ($location) {
                $query->where('city', '=', $location);
                  });
}
    $professionals->whereHas('member', function ($query) use ($location) {
                $query->where('member_type', '=', 2);
                  })
                  ->paginate(20);

现在，对于每一行，您将拥有相关照片模型对象的集合