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".

3 Answers 3

3

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
1
  • 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, 2018 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
1
  • Like the entry abouve but the Concat() gives bad values. Apr 7, 2018 at 15:05
0

All these solutions may misuse GROUP BY clause

Some error may occur if sql_mode=only_full_group_by

Expression #1 of SELECT list is not in GROUP BY clause 
and contains nonaggregated column 'clone_magento_socoda.eav_attribute_option_value.option_id' 
which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by 

if you want all options sorted for each attribute, sorting would be a better way

SELECT
    eav_attribute_option.attribute_id, 
    eav_attribute.attribute_code,
    eav_attribute_option_value.option_id,
    eav_attribute_option_value.`value`,
    eav_attribute_option_value.store_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
    ORDER BY eav_attribute_option.attribute_id, eav_attribute.attribute_code, eav_attribute_option_value.`value` ASC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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