I'm sure this has been posted before, but I couldn't find an answer that really seemed to cover my problem.
I have two tables, with a one-to-one relation. One table has a few thousand records, the other one just a few. Simplified, it looks something like this:
Code:
Table Person (lots of records)
id - Long
name - String
{etc}
Table Subscription (handful of records)
id - Long
person_id - Long
{etc}
Now, I would like to use Criteria to get a set of persons, that does not have a record on the subscription table. Normally using SQL (postGres is this case), I'd write something like this:
Code:
select {Person columns} from person where id not in (select per.id from subscription sub inner join person per on per.id = sub.person_id)
Now getting that sql translated to criteria doesn't really seem to work for me (or rather, I can't get it to work properly). I've tried several things among the lines of:
Code:
DetachedCriteria criteria = DetachedCriteria.forClass(Person.class);
DetachedCriteria subCriteria = DetachedCriteria.forClass(Subscription.class);
subCriteria.createAlias("person", "person");
criteria.add(Subqueries.notIn("", subCriteria));
What am I doing wrong here?
Thanks in advance
Login
FAQ
Search