So, here’s my recommendation:
1) Make your “Connection” entity have a single integer primary key. Do not have a compound PK.
Is a compound PK going to prevent ERXExistsQualifier working in a case like this? I can make the change, just wondering why.
Ah, OK: it won't prevent ERXExistsQualifier working until I try to force it to generate an IN subquery in the SQL, at which point there are too many columns in the SELECT.
2) Use ERXExistsQualifier.
It should do the trick :-)
I'm trying... can you push me along a little bit based on the desired SQL above?
I think I got it. This is what I wanted:
SELECT t0.id, t0.id_no, t0.user_id FROM teacher t0 WHERE NOT EXISTS (SELECT 1 FROM connection e0 WHERE e0.teacher_id = t0.id AND e0.school_id = 1);
And this:
ERXQ.not(new ERXExistsQualifier(Connection.SCHOOL.is(this), Teacher.CONNECTIONS_KEY))
generates this:
SELECT t0.id, t0.id_no, t0.user_id FROM teacher t0 WHERE not ( EXISTS ( SELECT exists0.school_id, exists0.teacher_id FROM connection exists0 WHERE exists0.school_id = 1 AND exists0.teacher_id = t0.id ) );
Looks good to me. Thanks Aaron.