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.
