On 29 Jan 2015, at 2:04 pm, Aaron Rosenzweig <
email@hidden> wrote:
When you turn on the SQL adaptor debugging it will become clear what it is doing wrong. Then you’ll go “duh!"
The generated SQL is:
SELECT t0.id, t0.id_no, t0.user_id FROM teacher t0 INNER JOIN connection T1 ON t0.id = T1.teacher_id WHERE not (T1.school_id = 1);
(where I've manually substituted the bindings). What I think I need is something like this:
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);
Here’s the full reason why:
Just re-read that for the tenth time!
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.
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?