1

I want to get all values of all M1 Attribute Option values like in Magento backend. I have this code

SELECT
eav_attribute_option_value.option_id,
eav_attribute_option_value.`value`,
eav_attribute_option.attribute_id
            FROM
                    eav_attribute_option_value
            INNER JOIN eav_attribute_option ON eav_attribute_option_value.option_id = eav_attribute_option.option_id
            GROUP BY
                    eav_attribute_option_value.option_id
            ORDER BY
            eav_attribute_option_value.`value` ASC

But I am missing the values like "Store Label", "Sort Order" and "is Default", "Store ID".

2

Please note that Is Default is not a column but this value is exist in the main attribute table "eav_attribute", So you need also to join this table. The same for Store label: The Store label is the value itself but the difference is in the store_id.

Please try this query and modify it depending on your needs.

SELECT
eav_attribute_option_value.option_id,
eav_attribute_option_value.`value`,
eav_attribute_option_value.store_id,
eav_attribute_option.attribute_id,
eav_attribute_option.sort_order,
eav_attribute.default_value
            FROM
                    eav_attribute_option_value
            INNER JOIN eav_attribute_option ON eav_attribute_option_value.option_id = eav_attribute_option.option_id
            INNER JOIN eav_attribute ON eav_attribute.attribute_id = eav_attribute_option.attribute_id
            GROUP BY
                    eav_attribute_option_value.option_id,eav_attribute_option_value.`value`
            ORDER BY
            eav_attribute_option_value.`value` ASC
  • It looks like this should work. It is worth noting that if you are looking for a specific attribute's options, you can do that by adding where eav_attribute_option.attribute_id in (select attribute_id from eav_attribute where attribute_code = '[ENTER ATTRIBUTE CODE HERE]'); between the last inner join and the group by – mtr.web Apr 4 '18 at 13:27
0

Please try this query you can easily get all option values in 1 row instead different rows

SELECT 
    eav_attribute_option_value.option_id,
    GROUP_CONCAT(eav_attribute_option_value.value, ','),
    eav_attribute_option_value.store_id,
    eav_attribute_option.attribute_id,
    eav_attribute_option.sort_order,
    eav_attribute.default_value
FROM
    eav_attribute_option_value
        INNER JOIN
    eav_attribute_option ON eav_attribute_option_value.option_id = eav_attribute_option.option_id
        INNER JOIN
    eav_attribute ON eav_attribute.attribute_id = eav_attribute_option.attribute_id
GROUP BY eav_attribute_option.attribute_id
ORDER BY eav_attribute_option_value.value ASC
  • Like the entry abouve but the Concat() gives bad values. – Michael Müller Apr 7 '18 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.