26.08.2010 19:42, Victor Antonovich wrote:
Basically, I want to get by single request all records joined to some group but not joined to another.
Just a note - I have no problems to do this query by raw SQL. Main trouble at this moment is how to translate SQL query to equivalent Cayenne expression. Especially, its query part with relation negation.
So, let's imagine we have three tables - role, group and join table role_groups. These tables are mapped to two Cayenne object - Role and Group, linked together by flattened relationships in "many-to-many" manner.
How can be translated to Cayenne expression something about: "SELECT * FROM role t0 JOIN group_roles t1 ON (t0.id = t1.role_id) JOIN group t2 ON (t2.id = t1.group_id) LEFT JOIN group t3 ON (t3.name = 'Admin') LEFT JOIN group_roles t4 ON (t0.id = t4.role_id AND t3.id = t4.group_id) WHERE (t2.name = 'Active') AND (t4.id IS NULL)"?
Regards, Victor.
