Match entities fulfilling filter (strict superset of search)

Posted by Jon on Stack Overflow See other posts from Stack Overflow or by Jon
Published on 2010-05-24T19:56:42Z Indexed on 2010/05/25 5:51 UTC
Read the original article Hit count: 344

Filed under:
|
|
|
|

I have an entity (let's say Person) with a set of arbitrary attributes with a known subset of values. I need to search for all of these entities that match all my filter conditions. That is, given a set of Attributes A, I need to find all people that have a set of Attributes that are a superset of A.

For example, my table structures look like this:

Person:

id | name     
 1 | John Doe
 2 | Jane Roe
 3 | John Smith

Attribute:

id | attr_name 
 1 | Sex        
 2 | Eye Color 

ValidValue:

id | attr_id | value_name
 1 |       1 | Male      
 2 |       1 | Female
 3 |       2 | Blue
 4 |       2 | Green
 5 |       2 | Brown 

PersonAttributes

id | person_id | attr_id | value_id
 1 |         1 |       1 |        1
 2 |         1 |       2 |        3
 3 |         2 |       1 |        2
 4 |         2 |       2 |        4
 5 |         3 |       1 |        1
 6 |         3 |       2 |        4

In JPA, I have entities built for all of these tables. What I'd like to do is perform a search for all entities matching a given set of attribute-value pairs. For instance, I'd like to be able to find all males (John Doe and John Smith), all people with green eyes (Jane Roe or John Smith), or all females with green eyes (Jane Roe).

I see that I can already take advantage of the fact that I only really need to match on value_id, since that's already unique and tied to the attr_id. But where can I go from there? I've been trying to do something like the following, given that the ValidValue is unique in all cases:

select distinct p from Person p join p.personAttributes a 
where a.value IN (:values)

Then I've tried putting my set of required values in as "values", but that gives me errors no matter how I try to structure that.

I also have to get a little more complicated, as follows, but at this point I'd be happy with solving the first problem cleanly. However, if it's possible, the Attribute table actually has a field for default value:

id | attr_name | default_value
 1 | Sex       | 1
 2 | Eye Color | 5

If the value you're searching on happens to be the default value, I want it to return any people that have no explicit value set for that attribute, because in the application logic, that means they inherit the default value. Again, I'm more concerned about the primary question, but if someone who can help with that also has some idea of how to do this one, I'd be extremely grateful.

© Stack Overflow or respective owner

Related posts about sql

Related posts about orm