Блог

У меня есть следующий запрос:

 select "houses"."id", 
"houses"."uuid", 
COUNT(1) OVER() as full_count from "houses" 
CROSS JOIN LATERAL jsonb_array_elements(houses.types) house_types 
inner join "hoods" on "hoods"."id" = "houses"."hood_id" and "hoods"."owner_id" = 2 
inner join "groups" on "groups"."hood_id" = "hoods"."id" and "groups"."manager_id" = 54 
where house_types->>'type' = 'big' 
group by "houses"."id", "houses"."uuid" 
order by lower(houses.name) asc 
limit 20
  

Который правильно выдает мне первые 20 домов с типом ‘big’, которые находятся в блоке, идентификатор владельца которого равен 2, И с которым в блоке связана группа, менеджер которой равен 54.

Теперь проблема в том, что иногда у меня будут дома, название которых будет одинаковым, и я хочу просто сохранить один из них и удалить остальные. Так, например:

Если моя таблица домов выглядит как:

 id, types, name
1, [{ type: 'rating' }], 'white house'
2, [{ type: 'rating' }], 'white house'
3, [{ type: 'rating' }], 'red house'
  

Я бы просто получил строки с идентификаторами 1 и 3.

Какой хороший способ сделать это в PostgreSQL, предполагая, что к запросу могут применяться как смещения, так и ограничения, и я хочу удалить дубликаты.

Вместо group by используйте distinct on :

 select distinct on (lower(h.name)) h.id, h.uuid
      COUNT(*) OVER() as full_count
from houses h cross join lateral
     jsonb_array_elements(h.types) ht inner join
     "hoods" ho
     on ho.id = h.hood_id and
        ho.owner_id = 2 inner join
     "groups" g
     on g.hood_id = ho.id and
        g.manager_id = 54 
where house_types->>'type' = 'big' 
order by lower(houses.name) asc  
limit 20;
  

Редактировать:

 select h.*, count(*) over ()  as full_count
from (select distinct on (lower(h.name)) h.id, h.uuid
      from houses h cross join lateral
           jsonb_array_elements(h.types) ht inner join
           "hoods" ho
           on ho.id = h.hood_id and
              ho.owner_id = 2 inner join
           "groups" g
           on g.hood_id = ho.id and
              g.manager_id = 54 
      where house_types->>'type' = 'big' 
      order by lower(houses.name) asc  
     ) h
limit 20