Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Query with multiple matches in collection(One-To-Many relationship query)
Query with multiple matches in collection [message #558450] Sun, 12 September 2010 23:47 Go to next message
Miguel Missing name is currently offline Miguel Missing name
Messages: 9
Registered: September 2010
Junior Member
I have two tables, table1 has a one-to-many relationship to table2. The entity for table1 has a collection attribute with all related records in table2. How can I retrieve records in table1 that have multiple record matches in table2, for different combination of values? For example, I want all records in table 1 for which there are three different records in table2 with the field DESCRIPTION in table2 being either "BLACK", "WHITE", or "BLUE". All three colors must exist among the related records.

[Updated on: Mon, 13 September 2010 09:49]

Report message to a moderator

Re: Query with multiple matches in collection [message #558637 is a reply to message #558450] Mon, 13 September 2010 18:44 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

You can use JPQL, Criteria or Expressions for this.

The main point is that you need to join the OneToMany multiple times.

i.e.
Select t1 from Table1 t1 join t1.table2s t2a, join t1.table2s t2b, join t1.table2s t2c where t2a.color = 'black' and t2b.color = 'white' and t2c.color = 'blue'


James : Wiki : Book : Blog : Twitter
Re: Query with multiple matches in collection [message #558783 is a reply to message #558637] Tue, 14 September 2010 12:32 Go to previous message
Miguel Missing name is currently offline Miguel Missing name
Messages: 9
Registered: September 2010
Junior Member
Thanks, I ended up writing a native query to get out of the bind, but I will replace the code after I test your JPQL solution. I ended up doing a select subquery In native SQL language to retrieve the ids of the rows that had 3 matching records on the joined tables, then retrieving the entities using the id list. I will compare the performance once I write the JPQL method,.

The actual scenario is of course more complicated, with the criteria for each match in the joined tables involving multiple expressions for different columns in the tables. I had wondered whether I could implement a query by example using three different example objects for each criteria. That would greatly simplify the JPQL coding. Building expressions with the expression builder is not for the faint of heart.

[Updated on: Tue, 14 September 2010 12:44]

Report message to a moderator

Previous Topic:Transform foreign keys in One-To-One Relationship
Next Topic:MOXy example missing?
Goto Forum:
  


Current Time: Mon Sep 15 09:35:01 GMT 2014

Powered by FUDForum. Page generated in 0.01870 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software