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