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: [email protected]
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
