Grails query with associations - find all in a table where id is not in another table

Question

I need to be able to find all items in a table where the id of each item is not in a relational mapping table. In other words, I have one table where each row has an id. If that id is in a map table, it should not show up in my list.

I was thinking of querying my map table for all id's in it, then turning around and querying my main table to exclude any items that have that id... so something like:

select * from Main where id not in(select main_id from Map);

Is there a good way to do this with grails either through a findBy method or possibly a criteria builder query?

Answer 1

If you're using Hibernate, you should be able to do that with HQL, using a not in and a subquery (here's a page of examples):

from Main as main where main.id not in (select map.main_id from Map as map)

Based on this page, I believe you need to execute this query in a Main.findAll method:

def results = Main.findAll("from Main as main where main.id not in (select map.main_id from Map as map)")