Laravel 10中PostgreSQL查询distinct与orderBy联用报错求助

问题背景 #

使用Laravel 10框架、PostgreSQL数据库时，无法在查询中同时使用distinct()和orderBy()，尝试group_by又出现重复结果，网上方案均无效。查询代码如下：

$property = DB::table('properties')
            ->leftjoin('property_sub_types', 'property_sub_types.id', '=', 'properties.property_sub_type')
            ->leftjoin('property_images', 'properties.id', '=', 'property_images.property_id')
            ->leftjoin('cities', 'cities.id', '=', 'properties.city')
            ->select('properties.name as proName', 'properties.id as pro_id', 'properties.city', 'properties.street', 'property_sub_types.name as subName', 'property_images.image as imgName','cities.name as ctyName','properties.reserve_price','properties.description','properties.possession','properties.ownership','properties.auction_start_date','properties.contact_manager','properties.contact_number', 'properties.updated_at')
            ->where('properties.property_status', 4)
            ->where('properties.status', 1)
            ->where('property_sub_types.status', 1)
            ->orderBy('properties.updated_at')
            ->distinct('properties.id')
            ->paginate(30);

报错信息 #

SQLSTATE[42P10]: 无效列引用: 7 ERROR: SELECT DISTINCT ON 的表达式必须与初始 ORDER BY 表达式匹配 LINE 1: select distinct on ("properties"."id")

解决方案 #

原因说明 #

PostgreSQL的DISTINCT ON语法有强制要求：排序的第一个字段必须和DISTINCT ON指定的字段完全一致，这是报错的核心原因。

方法一：调整排序顺序，符合PostgreSQL规则 #

把properties.id放在orderBy的首位，再追加你需要的updated_at排序，就能满足DISTINCT ON的要求：

$property = DB::table('properties')
            ->leftjoin('property_sub_types', 'property_sub_types.id', '=', 'properties.property_sub_type')
            ->leftjoin('property_images', 'properties.id', '=', 'property_images.property_id')
            ->leftjoin('cities', 'cities.id', '=', 'properties.city')
            ->select('properties.name as proName', 'properties.id as pro_id', 'properties.city', 'properties.street', 'property_sub_types.name as subName', 'property_images.image as imgName','cities.name as ctyName','properties.reserve_price','properties.description','properties.possession','properties.ownership','properties.auction_start_date','properties.contact_manager','properties.contact_number', 'properties.updated_at')
            ->where('properties.property_status', 4)
            ->where('properties.status', 1)
            ->where('property_sub_types.status', 1)
            ->orderBy('properties.id') // 先按DISTINCT ON指定的字段排序
            ->orderBy('properties.updated_at') // 再按目标字段排序
            ->distinct('properties.id')
            ->paginate(30);

方法二：子查询先去重，再关联排序 #

如果不想改变最终的排序顺序，可以先通过子查询获取去重后的properties记录，再关联其他表并排序：

$property = DB::table(function($query) {
                $query->select('id', 'name', 'city', 'street', 'reserve_price', 'description', 'possession', 'ownership', 'auction_start_date', 'contact_manager', 'contact_number', 'updated_at', 'property_sub_type')
                      ->from('properties')
                      ->where('property_status', 4)
                      ->where('status', 1)
                      ->distinct('id');
            }, 'properties')
            ->leftjoin('property_sub_types', 'property_sub_types.id', '=', 'properties.property_sub_type')
            ->leftjoin('property_images', 'properties.id', '=', 'property_images.property_id')
            ->leftjoin('cities', 'cities.id', '=', 'properties.city')
            ->select('properties.name as proName', 'properties.id as pro_id', 'properties.city', 'properties.street', 'property_sub_types.name as subName', 'property_images.image as imgName','cities.name as ctyName','properties.reserve_price','properties.description','properties.possession','properties.ownership','properties.auction_start_date','properties.contact_manager','properties.contact_number', 'properties.updated_at')
            ->where('property_sub_types.status', 1)
            ->orderBy('properties.updated_at')
            ->paginate(30);

补充：解决group_by重复问题 #

用group_by时出现重复，是因为没有正确处理非分组字段。需要确保分组字段是properties.id（唯一标识），同时对关联表的非唯一字段（比如图片）使用聚合函数（如MAX()、MIN()）获取唯一值：

$property = DB::table('properties')
            ->leftjoin('property_sub_types', 'property_sub_types.id', '=', 'properties.property_sub_type')
            ->leftjoin('property_images', 'properties.id', '=', 'property_images.property_id')
            ->leftjoin('cities', 'cities.id', '=', 'properties.city')
            ->select(
                'properties.name as proName', 
                'properties.id as pro_id', 
                'properties.city', 
                'properties.street', 
                'property_sub_types.name as subName', 
                DB::raw('MAX(property_images.image) as imgName'), // 用聚合函数取唯一图片
                'cities.name as ctyName',
                'properties.reserve_price',
                'properties.description',
                'properties.possession',
                'properties.ownership',
                'properties.auction_start_date',
                'properties.contact_manager',
                'properties.contact_number', 
                'properties.updated_at'
            )
            ->where('properties.property_status', 4)
            ->where('properties.status', 1)
            ->where('property_sub_types.status', 1)
            ->groupBy(
                'properties.id', 
                'properties.name', 
                'properties.city', 
                'properties.street', 
                'property_sub_types.name', 
                'cities.name', 
                'properties.reserve_price',
                'properties.description',
                'properties.possession',
                'properties.ownership',
                'properties.auction_start_date',
                'properties.contact_manager',
                'properties.contact_number', 
                'properties.updated_at'
            )
            ->orderBy('properties.updated_at')
            ->paginate(30);

内容的提问来源于stack exchange，提问作者Yadhu Babu