cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tony Giaccone <tgiacc...@gmail.com>
Subject Re: Query across to-many relationship
Date Tue, 29 Jul 2014 03:07:44 GMT
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 


Mime
View raw message