2

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?

Community
  • 1
  • 1
Ben Muncey
  • 113
  • 7

1 Answers1

0

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)
...
Wolph
  • 78,177
  • 11
  • 137
  • 148
  • The problem with this is that the attributes for each row (att1 and att2) may appear the opposite way round, dependent on their positions in the 'eav_value' table, i.e. att1 for one row could be att2 for the next. I'm not sure how this can be prevented. – Ben Muncey Aug 10 '10 at 08:22
  • I've added an example for what I meant. Instead of using `att1` you will be using the actual `attribute_id`. Or even the name of the attribute. – Wolph Aug 10 '10 at 10:09
  • Thanks for the replies WoLpH. Should your code read 'SELECT v1.value_id AS $attribute_1' or is there a better way of obtaining the attribute? Using Zend Framework (php). – Ben Muncey Aug 10 '10 at 11:22
  • @Ben Muncey: Yes, that's exactly what I mean. I wasn't sure if you were talking about `PHP` or not so I didn't add example code. I would simply create a query like that and add it to your caching system. If some values can be empty in some cases than you can consider using a `LEFT JOIN` instead to make them nullable. – Wolph Aug 10 '10 at 11:36