nhibernatepostgresqlhqlicriteria

Using Postgres aggregate functions with NHibernate

I have the following query:

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

Sample output from this query:

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) 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?

(2) I'm using SQLite as my integration test database and PostgreSQL as my test/prod database. SQLite does not have the array_agg function, but does have a group_concat function that does something similar. Is it possible to set something up where I'll be able to use SQLite in my tests and PostgreSQL in test/prod?

(3) array_agg returns data as an array. I found a great article on nhibernate.info that explains how to extend NHibernate to handle PostgreSQL arrays. How do I include this in my criteria? For example, let's say I want to find a title that is in the Drama genre that is not a romantic drama.

Thanks in advance for any help!

Solution

  • (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?

    I don't think you should. Assuming you want to select all the titles by genre, you just need a WHERE clause that resolves the genre to its id number. For one reason, a subselect on a varchar column can use an index. For another reason, I'm pretty sure that by doing it this way, your question #3 just goes away.

    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

    It could also be written using an inner join on that same subquery.

    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?

    It's possible--and advisable--to use the same platform in development that you use in production. Install PostgreSQL and use it instead of SQLite.