CODEWITHSUNDEEP
mysql
David
David

Reputation: 3

How to use a proprety/value table in MySQL

I inherited a mysql database that has a table with columns like this:

object_id, property, value

It holds data like this:

1,first_name,Jane
1,last_name,Doe
1,age,10
1,color,red
2,first_name,Mike
2,last_name,Smith
2,age,20
2,color,blue
3,first_name,John
3,last_name,Doe
3,age,20
3,color,red
...

Basically what I want to do is treat this table as a regular table. How would I get the id numbers (or all properties) of a person who is age 20 sorted by last and than first name? So far I have:

SELECT object_id FROM table WHERE property = 'age' AND value = '20'
union
SELECT object_id FROM table WHERE property = 'color' AND value = 'red'

But I'm not sure how to go about ordering the data.

Thanks

Upvotes: 0

Views: 188

Answers (3)

Meredith L. Patterson
Meredith L. Patterson

Reputation: 4921

Unless you're prevented from doing so for some other reason (e.g., bureaucratic fiat), I'd normalize that table first, at least if the property names are consistent between "objects". Doing so will almost certainly mean having to rewrite existing queries, but any queries you write going forward will actually be sane and whoever follows you will at least stand a chance of being able to maintain them (and write new queries).

That said, if you're absolutely stuck with what you have, you can fake a pivot table on the fly approximately like this:

SELECT object_id, IF(property='first_name',value,NULL) AS first_name, IF(property='last_name',value,NULL) AS last_name, IF(property='age',value,NULL) AS age FROM table GROUP BY object_id;

and then use that in a sub-select. But as Donnie said, this will be horribly slow. I suppose you could whittle down the set of object_ids with an IN sub-select, but really there's no way you're going to get anything remotely resembling decent performance for the query you want out of a table like this.

(Another piece of horribleness -- numeric values like 'age' are stored as varchars?!?)

Upvotes: 0

Cade Roux
Cade Roux

Reputation: 89671

The difficulties of reconstituting rows in an EAV model is exactly what you are seeing.

You have to pivot the data from rows to columns to get to your "objects" and then query against it.

Typically a pivot can be done with:

SELECT object_id
    ,MAX(CASE WHEN property = 'age' THEN value ELSE NULL END) AS age
    ,MAX(CASE WHEN property = 'color' THEN value ELSE NULL END) AS color
-- ...
FROM EAVTABLE
GROUP BY object_id

Then:

SELECT *
FROM (
    SELECT object_id
        ,MAX(CASE WHEN property = 'age' THEN value ELSE NULL END) AS age
        ,MAX(CASE WHEN property = 'color' THEN value ELSE NULL END) AS color
    -- ...
    FROM EAVTABLE
    GROUP BY object_id
) AS objects
WHERE age = '20' -- Note you may want to cast from char to proper types above (and these casts can fail).
ORDER BY last_name, first_name

Upvotes: 1

Donnie
Donnie

Reputation: 46923

Property / value tables are a really bad idea, as you're discovering. They break the way SQL expects data to be presented, they can't be properly indexed, yadda yadda, etc. They're a sloppy fix for someone that doesn't feel like trying to maintain a database (or someone who good-intentionedly wants to give users "expandability" but doesn't think through all of the problems).

If it's at all an option, I strongly suggest converting this mess into normal tables. It might take a while, but it will be worth it.

As for your specific question, I'm not sure if mysql supports pivot or not. If it does, that will "rotate' your result set into a horizontal table which you can then order as you expect to be able to. If it doesn't, you can hack together a fake pivot with a lot of case statements and some aggregation to collapse all of the rows together ... but it's sloppy, and slow.

Upvotes: 0

Related Questions