cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Victor Antonovich <v.antonov...@gmail.com>
Subject Re: Queries against to-many relationships with 'NOT' subexpressions
Date Fri, 27 Aug 2010 09:04:43 GMT
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.

Mime
View raw message