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