MySQL self-join for an EAV based stock control application

Question

This question relates to the schema I suggested in my original question regarding a stock control application.

I'm trying to create a MySQL query that provides the current stock for a particular item.

The query is working but I wondered whether there is a more efficient way of obtaining the information I require.

SELECT 's'.*,
    'v1'.'attribute_id' AS 'att1',
    'v1'.'value' AS 'val1'
    'v2'.'attribute_id' AS 'att2',
    'v2'.'value' AS 'val2'
FROM 'eav_ev' AS 'ev1'
INNER JOIN 'stock' AS 's' ON s.id = ev1.stock_id
INNER JOIN 'eav_ev' AS 'ev2' ON ev1.stock_id = ev2.stock_id
INNER JOIN 'eav_value' AS 'v1' ON v1.id = ev1.value_id
INNER JOIN 'eav_value' AS 'v2' ON v2.id = ev2.value_id
WHERE (ev1.entity_id = '45')
    AND (ev1.value_id <> ev2.value_id)
    AND (s.total > 0)
GROUP BY 'ev1'.'stock_id'
ORDER BY 'ev1'.'value_id' ASC

This returns something along the lines of

array (1) {
    [0] => array(5) {
        ["stock_id"] => "2"
        ["att1"] => "3"
        ["val1"] => "M12"
        ["att2"] => "4"
        ["val2"] => "45"
    }
}

It seems very messy but my poor brain is incapable of coming up with something better.

Any suggestions?

Answer 1

Instead of using attribute_id AS att1 you could also use value AS attribute_X if you store a list of attributes first. You can simply cache the query after which you can just select all needed data in 1 clear query.

Assuming you've fetched a list of attribute IDs first (i.e. SELECT attribute_id FROM eav_value), select this:

SELECT
    v1.value_id AS attribute_1 -- (or whatever the ID was fetched in the first query)
    v2.value_id AS attribute_2 -- (or whatever the second ID was fetched in the first query)
...

Answer 2

After some searching I came across the following question/article.

http://stackoverflow.com/questions/1164746/zend-select-with-self-join-overwriting-fields

I guess this goes some way to answering my own question. It looks as though it is necessary to 'post process' the query to assign the attributes correctly. Fairly easy to do, but seems messy.