You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Tony Giaccone <to...@giaccone.org> on 2014/07/27 05:55:01 UTC
Query across to-many relationship
I’m a bit lost on how to make this work. I hope someone can point me in the right direction.
I have this model:
A has a to one relationship to B
B has a to many relationship to C
C has several different attributes (type, quantity, magnitude) that I want to select against.
My query is I want the set of A, where B has a collection of C that contains both (type 1, quantity 5) and (type 2, magnitude 7) and the size of B’s collection of C is 4;.
obviously the exact values described here are arbitrary, but I hope you get the idea.
I’m at a loss for how to structure this query in Cayenne. Any suggestions?
Tony
Re: Query across to-many relationship
Posted by Tony Giaccone <tg...@gmail.com>.
After consulting with some of my more seasoned SQL gurus. The answer seems to be using the INTERSECT operator.
I want to model this query..
select *
from MAIN
where MAIN_ID in (
select MAIN_ID"
from TIER
where TIER_SIZE ='S' and COLOR = 'RED'
INTERSECT
select MAIN_ID
from TIER
where TIER_SIZE ='L' and COLOR = 'BLUE’
)
I don’t see a way to model the intersect in cayenne using expressions, but I could use a variation on the form shown below.
Tony
On Jul 27, 2014, at 6:10 AM, dollj@xsinet.co.za wrote:
> Hi Tony
>
> I don't think you can get this type of result through the usual relationships on Object Entities.
> So you'll have to do it using a custom query, like so:
>
> String sqlString = "select * from A where B_ID in (select B_ID from C "+
> "where (type='$type1' and quantity='$qty1') or (type='$type2' and magnitude='$mag2') "+
> "group by B_ID having count(*) = 4)";
>
> SQLTemplate templateQry = SQLTemplate( A.class, sqlString );
>
> HashMap<String, Object> parameters = new HashMap<>();
> parameters.put( "type1", "1" );
> parameters.put( "qty1", "5" );
> parameters.put( "type2", "2" );
> parameters.put( "mag2", "7" );
>
> templateQry.setParameters( parameters );
>
> List<A> alist = context.performQuery( templateQry );
>
>
> If you are going to run this query multiple times then just create the templateQry once and then change the values in the parameters HashMap for each context.performQuery( templateQry ).
>
> Regards
> Jurgen
>
>
>
>
> -----Original Message----- From: Tony Giaccone
> Sent: Sunday, July 27, 2014 5:55 AM
> To: user@cayenne.apache.org
> Subject: Query across to-many relationship
>
> I’m a bit lost on how to make this work. I hope someone can point me in the right direction.
>
> I have this model:
>
> A has a to one relationship to B
> B has a to many relationship to C
> C has several different attributes (type, quantity, magnitude) that I want to select against.
>
>
> My query is I want the set of A, where B has a collection of C that contains both (type 1, quantity 5) and (type 2, magnitude 7) and the size of B’s collection of C is 4;.
>
> obviously the exact values described here are arbitrary, but I hope you get the idea.
>
> I’m at a loss for how to structure this query in Cayenne. Any suggestions?
>
>
> Tony
Re: Query across to-many relationship
Posted by do...@xsinet.co.za.
Hi Tony
I don't think you can get this type of result through the usual
relationships on Object Entities.
So you'll have to do it using a custom query, like so:
String sqlString = "select * from A where B_ID in (select B_ID from C "+
"where (type='$type1' and quantity='$qty1') or (type='$type2' and
magnitude='$mag2') "+
"group by B_ID having count(*) = 4)";
SQLTemplate templateQry = SQLTemplate( A.class, sqlString );
HashMap<String, Object> parameters = new HashMap<>();
parameters.put( "type1", "1" );
parameters.put( "qty1", "5" );
parameters.put( "type2", "2" );
parameters.put( "mag2", "7" );
templateQry.setParameters( parameters );
List<A> alist = context.performQuery( templateQry );
If you are going to run this query multiple times then just create the
templateQry once and then change the values in the parameters HashMap for
each context.performQuery( templateQry ).
Regards
Jurgen
-----Original Message-----
From: Tony Giaccone
Sent: Sunday, July 27, 2014 5:55 AM
To: user@cayenne.apache.org
Subject: Query across to-many relationship
I’m a bit lost on how to make this work. I hope someone can point me in the
right direction.
I have this model:
A has a to one relationship to B
B has a to many relationship to C
C has several different attributes (type, quantity, magnitude) that I want
to select against.
My query is I want the set of A, where B has a collection of C that contains
both (type 1, quantity 5) and (type 2, magnitude 7) and the size of B’s
collection of C is 4;.
obviously the exact values described here are arbitrary, but I hope you get
the idea.
I’m at a loss for how to structure this query in Cayenne. Any suggestions?
Tony