sqlalchemy：获取值不存在的ID

Question

有一个tablewith columns listing_ids，keys如何获取listing_ids（和相应的缺失键）其中list_of_values不存在的值keys？list_of_values = [key2,key3]桌子listing_id  keys424         key1424         key2424         key3523         key12433        key22433        key153          key23           key3我需要得到以下结果：listing_id  keys_that_does_not_exist523         key2523         key32433        key353          key33           key2我试过：ids_without_keys_q = session.query(Table)\                             .filter(~ exists().where(Table.key.in_(list_of_values))我在用postgresql

Answer 1

问题是您期望返回多少个值。PostgreSQL 对生成的数据不是很好，就像这个解决方案使用的那样，所以如果它太慢，只获取所有组合的列表并使用 Python 查找不存在的组合可能会更快。

另外，我在这里根据您的查询假设每个 listing_id/key 对有一行，并且您没有将键存储为字符串数组。如果是这样，请告诉我，我会修改答案。

首先，我假设您不想要没有匹配项的 ID，因此您可以像这样构造它而不是生成列表。我使用 来func.count()过滤掉所有与所有键匹配的列表：

unique_incomplete_listings = session.query(Table.listing_id.label('listing_id'))

  .group_by(Table.listing_id)

  .having(func.count() < 3)

其次，将其转换为CTE，然后从查询中获取 (listing, key) 的所有可能组合：

from sqlalchemy.dialects.postgresql import array

unique_incomplete_listings = unique_incomplete_listings.cte()

all_potential_matches = session.query(

    unique_incomplete_listings.c.listing_id,

    # this creates a cartesian product of listing_ids to elements in the array

    func.unnest(array(['key1', 'key2', 'key3']))

)

使用EXCEPT删除您在数据库中找到的任何匹配项

query = all_potential_matches.except_all(

  session.query(Table.listing_id, Table.key)

  # We join here to prevent doing extra work if the listing does have all keys.

  # If this makes the query slower, consider making the CTE a subquery and

  # removing this join instead

  .join(unique_incomplete_listings,

        unique_incomplete_listings.c.listing_id == Table.listing_id)

)