cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <>
Subject Re: Query across to-many relationship
Date Sun, 27 Jul 2014 10:10:01 GMT
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 ).


-----Original Message----- 
From: Tony Giaccone
Sent: Sunday, July 27, 2014 5:55 AM
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?


View raw message