I want to extract results from EAV (entity-attribute-value) tables, or more specifically entity-metadata tables (think like wordpress wp_posts and wp_postmeta) as a "nicely formatted relational table", in order to do some sorting and/or filtering.
I've found some examples of how to format the results within the query (as opposed to writing 2 queries and joining the results in code), but I would like to know the "most efficient" method for doing so, especially for larger result sets.
And when I say "most efficient", I mean for something like the following scenarios:
Get all Entities with last name like XYZ
Return a list of Entities sorted by birthday
e.g. turn this:
** ENTITY ** ----------------------- ID | NAME | whatever ----------------------- 1 | bob | etc 2 | jane | etc 3 | tom | etc ** META ** ------------------------------------ ID | EntityID | KEY | VALUE ------------------------------------ 1 | 1 | first name | Bob 2 | 1 | last name | Bobson 3 | 1 | birthday | 1983-10-10 . | 2 | first name | Jane . | 2 | last name | Janesdotter . | 2 | birthday | 1983-08-10 . | 3 | first name | Tom . | 3 | last name | Tomson . | 3 | birthday | 1980-08-10
into this:
** RESULTS ** ----------------------------------------------- EID | NAME | first name | last name | birthday ----------------------------------------------- 1 | bob | Bob | Bobson | 1983-10-10 2 | jane | Jane | Janesdotter | 1983-08-10 3 | tom | Tom | Tomson | 1980-08-10
so I can sort or filter by any of the meta fields.
I found some suggestions here, but