Not able to use orderBy in Laravel query with distinct

Question

Hi I am using Laravel 10 . My issue is not able to use orderby() with distinct().

I am using PostgreSQL as database. When I tried Group_by , I am getting repeated results. I have tried many solutions found here, but nothing works for me.

My Query

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

Error showing as

SQLSTATE[42P10]: Invalid column reference: 7 ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1: select distinct on ("properties"."id")

@SabinChacko ->orderBy('properties.id') ->orderBy('properties.updated_at') I have added this and getting results also but, results orderby properties.id not by updated_at — Yadhu Babu
– Yadhu Babu, Commented Jul 6, 2023 at 9:23
You haven't explained very clearly what you want to do. Maybe you want to use distinct() with no argument. — jjanes
– jjanes, Commented Jul 6, 2023 at 11:51

Parth_009 · Accepted Answer · 2023-07-06 12:10:47Z

0

You can apply distinct on name column of properties table to get distinct properties value. I hope this can be helpful.

answered Jul 6, 2023 at 12:10

Parth_009

916 bronze badges

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

Collectives™ on Stack Overflow

Not able to use orderBy in Laravel query with distinct

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related