Filter entities that match all pairs
- by Jon
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.
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?
