连接查询中仅包含in子句中所有值的列 [英] Join query with only columns that have all values in `in` clause

问题描述

我正在为我的网站创建一个简单的过滤系统。场地和便利设施之间存在许多关系。这是我的表。

I'm creating a simple filtering system for my website. I have a many to many relationship between venues and amenities. Here are my tables.

注意：所有ID均为uuid。

NOTE: all ids are uuids. Making them short for simplicity

地点：

| id    |      name      |
_________________________
| 'aaa' |  'first venue' |
| 'bbb' | 'second venue' |
| 'ccc' | 'third venue'  |

便利设施：

| id    |      name        |
___________________________
| 'aaa' |  'first amenity' |
| 'bbb' | 'second amenity' |
| 'ccc' | 'third amenity'  |

便利设施地点：

| amenity_id  |    venue_id  |
______________________________
| 'aaa'       |  'aaa'       |
| 'bbb'       | 'aaa'        |
| 'ccc'       | 'aaa'        |
| 'aaa'       | 'bbb'        |
| 'bbb'       | 'ccc'        |

我正在尝试编写查询以返回至少已传递所有amenity_ids的场所。例如，传递amenity_ids aaa 和 bbb 。

I'm trying to write a query to return the venues that have at least all the passed in amenity_ids. For example passing in amenity_ids aaa and bbb.

我正在寻找何时传递的便利ID是 aaa 和 bbb 的输出。

Output I'm looking for when the amenity ids passed in are aaa and bbb.

| id    |      name      |
_________________________
| 'aaa' |  'first venue' |

最初我尝试了此查询

select * from venues 
INNER JOIN amenity_venue ON amenity_venue.venue_id = venues.id
where amenity_id in ('aaa', 'bbb');

这将返回所有amenity_id aaa 或 bbb

This returns all the venues that have either amenity_id aaa or bbb

| id    |      name      |
_________________________
| 'aaa' |  'first venue' |
| 'bbb' | 'second venue' |
| 'ccc' | 'third venue'  |

所以天真地尝试了

select * from venues 
INNER JOIN amenity_venue ON amenity_venue.venue_id = venues.id
where amenity_id = 'aaa'
  and amenity_id = 'bbb';

什么都不返回。我正在尝试编写一个查询，其中如果amenity_ids aaa 和 bbb 仅在场所 aaa 被返回，因为它是唯一与这两种便利设施都有关系的场所。便利设施的数量也因查询而异。

Which returns nothing. I'm trying to write a query where if amenity_ids aaa and bbb are passed in only venue aaa is returned since its the only venue that has a relationship with both amenities. Also the number of amenities is dynamic from query to query.

推荐答案

您可以通过将ID汇总到一个数组中，然后将其与预期的ID列表进行比较：

You can do this by aggregating the IDs into an array and then compare that with the list of intended IDs:

select v.*
from venues v
  join amenity_venue av ON av.venue_id = v.id
group by v.id
having array_agg(av.amenity_id) @> array['aaa', 'bbb'];

以上假设 venue.id 为声明为主键（由于 group by ）。

The above assumes that venue.id is declared as the primary key (because of the group by).

如果您只想传递便利设施名称，则实际上不需要对查询中的ID进行硬编码：

You don't really need to hardcode the IDs in the query if you would like to just pass the amenity names:

select v.*
from venues v
  join amenity_venue av ON av.venue_id = v.id
group by v.id
having array_agg(av.amenity_id) @> array(select id 
                                         from amenities 
                                         where name in ('first amenity', 'second amenity'));

在线示例： https://rextester.com/FNNVXO34389

这篇关于连接查询中仅包含in子句中所有值的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！