Level 73
Try adding the properties.updated_at to the select.
Jul 6, 2023
7
Level 1
Not able to use orderby with distinct in laravel Query
Hi all, I am using Laravel 10. Given below is my Laravel 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')
->where('properties.property_status', 4)
->where('properties.status', 1)
->where('property_sub_types.status', 1)
->distinct('properties.id')
->paginate(30);
When I use
->orderBy('properties.updated_at', 'desc')
in query showing error
SQLSTATE[42P10]: Invalid column reference: 7 ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1: select distinct on ("properties"."id") "properties"."name" a... ^ (Connection: pgsql, SQL: select distinct on ("properties"."id") "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" from "properties" left join "property_sub_types" on "property_sub_types"."id" = "properties"."property_sub_type" left join "property_images" on "properties"."id" = "property_images"."property_id" left join "cities" on "cities"."id" = "properties"."city" where "properties"."property_status" = 4 and "properties"."status" = 1 and "property_sub_types"."status" = 1 order by "properties"."updated_at" desc limit 30 offset 0)
Level 1
@Tray2 Same error SQLSTATE[42P10]: Invalid column reference: 7 ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1: select distinct on ("properties"."id") "properties"."name" a...
Level 73
@radhamadhavamhostel This should help you
https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by
It seems that DISTINCT ON and ORDER BY don't play well together.
One solution seems to be making an inline view from your query and order on that.
SELECT * FROM (
SELECT DISTINCT ON (address_id) *
FROM purchases
WHERE product_id = 1
ORDER BY address_id, purchased_at DESC
) t
ORDER BY purchased_at DESC
1 like
Level 122
and you only need to use unique because of the joins? How does the database know which of the results it should take?
Did you not consider eloquent and nested relationships for this?
Level 1
@Snapey Yes, because of these joins I am getting duplicate records. I tried groupby which is also not working
Level 122
@radhamadhavamhostel So why not use eloquent?
1 like
Level 1
@Snapey Thanks for the suggestion its working. Can you help how to add select with alias to this
$property = Property::with(['subType', 'images', 'city'])
->where('property_status', 4)
->where('status', 1)
->whereHas('subType', function ($query) {
$query->where('status', 1);
})
->orderBy('updated_at', 'desc')
->paginate(30);
up to properties.street everthing working fine
->select('properties.name as proName','properties.city','properties.id as pro_id', 'properties.street', 'property_sub_types.name as subName')
After adding property_sub_types.name as subName to select cause error "SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "property_sub_types" LINE 1: ...perties"."id" as "pro_id", "properties"."street", "property_.."
Please or to participate in this conversation.