nhibernate - 在 NHibernate 中使用 Postgres 聚合函数

Question

我有以下查询:

SELECT title_id, title, array_agg(g.name)
FROM title t
INNER JOIN title_genre tg USING(title_id)
INNER JOIN genre g USING (genre_id)
GROUP BY title_id, title
ORDER BY title_id
LIMIT 10

此查询的示例输出:

5527;"The Burbs";"{Suspense,"Dark Humor & Black Comedies",Comedy,"Cult Comedies"}"5528;"20,000 Leagues Under the Sea";"{"Family Adventures","Children & Family","Ages 5-7","Book Characters","Family Animation"}"5529;"2001: A Space Odyssey";"{"Classic Sci-Fi & Fantasy","Sci-Fi Thrillers",Classics}"5530;"2010: The Year We Make Contact";"{"Sci-Fi Dramas","Alien Sci-Fi","Sci-Fi & Fantasy","Dramas Based on Contemporary Literature","Psychological Thrillers","Dramas Based on the Book"}"5531;"The 39 Steps";"{"Dramas Based on the Book","United Kingdom",Thrillers,"Espionage Thrillers","Dramas Based on Classic Literature",Suspense}"5532;"4D Man";"{"Classic Sci-Fi & Fantasy","Sci-Fi & Fantasy","Sci-Fi Horror"}"5533;"8 Seconds";"{Drama,"Romantic Dramas",Biographies,"Indie Dramas","Sports Dramas","Miscellaneous Sports","Sports Stories","Other Sports"}"5534;"9 1/2 Weeks";"{"Steamy Romance",Romance,"Romantic Dramas"}"5535;"About Last Night...";"{"Romantic Dramas","Romantic Comedies",Romance}"5536;"Above the Law";"{"Action & Adventure","Action Thrillers","Martial Arts"}"

(1) 如何围绕 array_agg 函数创建 NHibernate 条件？我是否需要以任何方式扩展 PostgreSQL 方言以适应这种情况？

(2) 我使用 SQLite 作为我的集成测试数据库，使用 PostgreSQL 作为我的测试/产品数据库。 SQLite 没有 array_agg 函数，但有一个 group_concat 函数可以做类似的事情。是否可以设置一些东西，使我能够在我的测试中使用 SQLite 并在测试/生产中使用 PostgreSQL？

(3) array_agg 以数组形式返回数据。我在 nhibernate.info 上找到了一篇很棒的文章这解释了如何扩展 NHibernate 来处理 PostgreSQL 数组。我如何将其包含在我的标准中？例如，假设我想找到一个不是浪漫剧的戏剧类型的标题。

在此先感谢您的帮助!

Answer 1

(1) How do I create a NHibernate criteria around the array_agg function? Will I need to extend the PostgreSQL dialect in any way to accommodate this?

我认为你不应该。假设您想要按流派选择所有标题，您只需要一个 WHERE 子句将流派解析为它的 ID 号。出于一个原因，varchar 列上的子选择可以使用索引。出于另一个原因，我很确定通过这样做，您的问题 #3 就消失了。

SELECT title_id, title, array_agg(g.genre)
FROM title t
INNER JOIN title_genre tg USING(title_id)
INNER JOIN genre g USING (genre_id)
WHERE tg.title_id in (SELECT title_id 
                      FROM title_genre 
                      INNER JOIN genre ON genre.genre_id = title_genre.genre_id 
                                      AND genre.genre = 'Suspense'
                      )
GROUP BY title_id, title
ORDER BY title_id
LIMIT 10

它也可以在同一个子查询上使用内部连接来编写。

SELECT t.title_id, t.title, array_agg(g.genre)
FROM title t
INNER JOIN title_genre tg USING(title_id)
INNER JOIN genre g USING (genre_id)
INNER JOIN (SELECT title_id 
            FROM title_genre 
            INNER JOIN genre ON genre.genre_id = title_genre.genre_id 
                            AND genre.genre = 'Suspense'
            ) gn
            ON gn.title_id = tg.title_id
GROUP BY t.title_id, t.title
ORDER BY t.title_id
LIMIT 10

(2) Is it possible to set something up where I'll be able to use SQLite in my tests and PostgreSQL in test/prod?

在开发中使用您在生产中使用的相同平台是可能的，也是可取的。安装 PostgreSQL 并使用它代替 SQLite。