You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Victor Antonovich <v....@gmail.com> on 2010/08/24 13:05:50 UTC
Queries against to-many relationships with 'NOT' subexpressions
Hello!
My (simplified) Cayenne scheme has two kind of objects - Role's and
Group's with many-to-many (flattened) relationships. Main task is to
check is this Role member of some Group (i.e. 'Active') while it is not
member of another group (i.e. 'Admin'). Aliased query expression looks like:
((a.name='Active') and (not (a.name='Admin')))
where `a` is alias for `role.groups` to-many relationship to Group entities.
But this query doesn't work as expected - roles in 'Admin' group will be
returned. This is because DB query with double-joined Group table
selects record with self-joined 'Active' group name.
So question is simple - is there any way to do such queries?
Thanks,
Victor.
Re: Queries against to-many relationships with 'NOT' subexpressions
Posted by Victor Antonovich <v....@gmail.com>.
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.
Re: Queries against to-many relationships with 'NOT' subexpressions
Posted by Victor Antonovich <v....@gmail.com>.
Hello!
25.08.2010 03:45, Aristedes Maniatis wrote:
>
> On the page I referenced you can see two important ideas. A split join
> (aliased path) will let you create two different joins so that you can
> separate out your two parts of the query. And an outer join is usually
> what you need when you want to find the negation of a relationship. But
> that depends on whether you mean:
>
> 1. find all records which are joined to records other than 'admin', OR
> 2. find all records which are not joined to 'admin' (including records
> which aren't joined to anything)
>
> depending on which one you are after will determine how you need to
> build this query
>
>
> Ari
>
Basically, I want to get by single request all records joined to some
group but not joined to another.
Regards,
Victor.
Re: Queries against to-many relationships with 'NOT' subexpressions
Posted by Aristedes Maniatis <ar...@maniatis.org>.
On 25/08/10 12:57 AM, Victor Antonovich wrote:
> Hello!
>
> 24.08.2010 16:05, Aristedes Maniatis wrote:
>
>> You need to create an outer join.
>>
>> http://cayenne.apache.org/doc/path-expressions.html
>>
>
> Thanks for the response. AFAIR outer join can widen query results, but I need to narrow them excluding joining of group records with same ID.
>
> Could You explain usage of outer join for such case?
On the page I referenced you can see two important ideas. A split join (aliased path) will let you create two different joins so that you can separate out your two parts of the query. And an outer join is usually what you need when you want to find the negation of a relationship. But that depends on whether you mean:
1. find all records which are joined to records other than 'admin', OR
2. find all records which are not joined to 'admin' (including records which aren't joined to anything)
depending on which one you are after will determine how you need to build this query
Ari
--
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
Re: Queries against to-many relationships with 'NOT' subexpressions
Posted by Victor Antonovich <v....@gmail.com>.
Hello!
24.08.2010 16:05, Aristedes Maniatis wrote:
> You need to create an outer join.
>
> http://cayenne.apache.org/doc/path-expressions.html
>
Thanks for the response. AFAIR outer join can widen query results, but I
need to narrow them excluding joining of group records with same ID.
Could You explain usage of outer join for such case?
Regards,
Victor.
Re: Queries against to-many relationships with 'NOT' subexpressions
Posted by Aristedes Maniatis <ar...@maniatis.org>.
You need to create an outer join.
http://cayenne.apache.org/doc/path-expressions.html
On 24/08/10 9:05 PM, Victor Antonovich wrote:
> Hello!
>
> My (simplified) Cayenne scheme has two kind of objects - Role's and Group's with many-to-many (flattened) relationships. Main task is to check is this Role member of some Group (i.e. 'Active') while it is not member of another group (i.e. 'Admin'). Aliased query expression looks like:
>
> ((a.name='Active') and (not (a.name='Admin')))
>
> where `a` is alias for `role.groups` to-many relationship to Group entities.
>
> But this query doesn't work as expected - roles in 'Admin' group will be returned. This is because DB query with double-joined Group table selects record with self-joined 'Active' group name.
>
> So question is simple - is there any way to do such queries?
>
> Thanks,
> Victor.
--
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A