Grouping problems. How to do the same in PostgreSQL?

Question

PostgreSQL

There is a request for data on residential complexes (LC).

Parameters related not only to residential complexes, but also to apartments in them, as well as buildings and sections, can be dynamically substituted into the query conditions.

On MySQL, it immediately became clear that joining apartments (as well as buildings and sections) to residential complexes is astronomically long, simply because there are a lot of apartments, but few complexes. It is much faster to do the opposite - to take apartments and attach residential complexes to them, as well as all other entities by availability. As long as the LCD has apartments, everything is in order. Let's skip this shortcoming.

And, since I use the YII2 + ActiveRecord framework, the resulting apartment object simply serves as a layer through Relation to access the model and data of the residential complex.

The result of the Query Builder is a query that works great in MySQL. Removed additional conditions, so as not to confuse.

SELECT "flat".* FROM "flat" 
LEFT JOIN "complex" ON "flat"."complex_id" = "complex"."id"
LEFT JOIN "building" ON "flat"."building_id" = "building"."id"
LEFT JOIN "section" ON "flat"."section_id" = "section"."id"
GROUP BY "complex"."id" 
ORDER BY "complex"."rate" DESC, "flat"."price_call", "complex"."room_price_min" 
LIMIT 30;

It is important to note that in order to get one apartment for each residential complex, you need to group the data by the GROUP BY "complex"."id" identifier.

Everything works great.

But in PostgreSQL 12 GROUP BY does not know how to do this:

Grouping error: 7 ERROR: column "flat.id" must appear in the GROUP BY clause or be used in an aggregate function

PostgreSQL asks to add flat.id to the grouping conditions. I'm not sorry, but this leads to the fact that SELECT again returns all the apartments and at the output I have tens of thousands of identical LCDs.

How can a similar problem be solved in Postgres?

Answer 1

SELECT * FROM (
  SELECT DISTINCT ON ("complex"."id") "complex".*, "flat".*
  FROM "flat" 
  LEFT JOIN "complex" ON "flat"."complex_id" = "complex"."id"
  LEFT JOIN "building" ON "flat"."building_id" = "building"."id"
  LEFT JOIN "section" ON "flat"."section_id" = "section"."id" 
  ORDER BY "complex"."id", "flat"."price_call"
) q
 ORDER BY rate DESC, room_price_min
 LIMIT 30

Answer 2

Your query is invalid, when grouping by you can't select "flat".* you should select "complex"."id", "complex"."rate" , In case you need to use aggregate functions with apartment data MIN("flat "."price_call")

SELECT "complex"."id",  "complex"."rate", MIN("flat"."price_call") "min_flat_price_call" 
FROM "flat" 
LEFT JOIN "complex" ON "flat"."complex_id" = "complex"."id"
LEFT JOIN "building" ON "flat"."building_id" = "building"."id"
LEFT JOIN "section" ON "flat"."section_id" = "section"."id"
GROUP BY "complex"."id",  "complex"."rate" 
ORDER BY 
    "complex"."rate"  DESC, 
    "min_flat_price_call",  
    "complex"."room_price_min" 
LIMIT 30;

Answer 3

Try ranking.

select 
*
from
(
  SELECT 
  flat.*,
  row_number() over (partition by complex.id order by  complex.rate DESC, flat.price_call, complex.room_price_min) as nn
  FROM flat 
  LEFT JOIN complex ON flat.complex_id = complex.id
  LEFT JOIN building ON flat.building_id = building.id
  LEFT JOIN section ON flat.section_id = section.id
) as x
where x.nn = 1
LIMIT 30;