Home » Support » Forum » Query to search for common elements in two separate collections.

Query to search for common elements in two separate collections.

#1

I'm trying to work out the most efficient way to build a query that can compare two collections for identical members.

Something like:

SELECT o FROM Object o WHERE o.someCollection CONTAINS MEMBER OF :someCollection

Is something like this possible in the JPA spec?

#2

A possible efficient way is to use some sort of hash function for the collections and to retrieve candidate collections by identical hash code. Candidate collections should then be further filtered by checking for equality.

The hash code of every stored collection should be stored in an indexed field, possibly in a new entity or embeddable class that would wrap the collection with its hash code.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
#3

Sounds like you are suggesting a solution to matching the entire contents of a collection? if so, that's not what I was after. I just want to check if a collection contains an element that is also present in another collection.

Right now I just do this by iteration of the smaller sized collection retrieving results for matches that contain the element for each iteration until all have been checked. I just wasn't sure if there was something in the JPA that allowed me to put this into the query ie "select FROM Object o WHERE (Iterate through :collection) IN o.collection", I hope that explanation makes sense.

Right now I am just doing:

for(String s : collection) {
  "select FROM Object o WHERE :s IN o.collection"
}
#4

> Sounds like you are suggesting a solution to matching the entire contents of a collection?

Yes.

> if so, that's not what I was after. I just want to check if a collection contains an element that is also present in another collection.

This is easier and supported by JPA:

SELECT o FROM MyClass o JOIN o.someCollection e WHERE e MEMBER OF :someCollection
ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
#5

That worked great, thanks!


Post Reply

To post a reply and/or subscribe to update notifications - please login