Magento 2: SQL select all attributes for all products
0
I need a SQL Query that pulls all attributes for all products.
I have the bellow code for M1 but it does not work (of cause) for M2. But I need the exact counterpart. After uploading all products into our M2 shop we want to compare for errors.
So I need: SKU, attribute_ID, attribute_text
Old M1 query:
SELECT
*
FROM
(SELECT
ce.sku,
ea.attribute_id,
ea.attribute_code,
CASE ea.backend_type WHEN 'varchar' THEN ce_varchar.value WHEN 'int' THEN ce_int.value WHEN 'text' THEN ce_text.value WHEN 'decimal' THEN ce_decimal.value WHEN 'datetime' THEN ce_datetime.value ELSE ea.backend_type END AS value,
CASE ea.backend_type WHEN 'varchar' THEN ce_varchar.store_id WHEN 'int' THEN ce_int.store_id WHEN 'text' THEN ce_text.store_id WHEN 'decimal' THEN ce_decimal.store_id WHEN 'datetime' THEN ce_datetime.store_id ELSE ea.backend_type END AS store_id,
ea.is_required AS required
FROM
catalog_product_entity AS ce
LEFT JOIN eav_attribute AS ea ON ce.entity_type_id = ea.entity_type_id
LEFT JOIN catalog_product_entity_varchar AS ce_varchar ON ce.entity_id = ce_varchar.entity_id AND ea.attribute_id = ce_varchar.attribute_id AND ea.backend_type = 'varchar'
LEFT JOIN catalog_product_entity_int AS ce_int ON ce.entity_id = ce_int.entity_id AND ea.attribute_id = ce_int.attribute_id AND ea.backend_type = 'int'
LEFT JOIN catalog_product_entity_text AS ce_text ON ce.entity_id = ce_text.entity_id AND ea.attribute_id = ce_text.attribute_id AND ea.backend_type = 'text'
LEFT JOIN catalog_product_entity_decimal AS ce_decimal ON ce.entity_id = ce_decimal.entity_id AND ea.attribute_id = ce_decimal.attribute_id AND ea.backend_type = 'decimal'
LEFT JOIN catalog_product_entity_datetime AS ce_datetime ON ce.entity_id = ce_datetime.entity_id AND ea.attribute_id = ce_datetime.attribute_id AND ea.backend_type = 'datetime'
WHERE
1=1) AS tab
WHERE
tab.value != ""
magento2 attributes sql
edited yesterday
Oshan
1,3961919
asked Jun 16 '18 at 18:54
Michael MüllerMichael Müller
3910
add a comment |
0
I need a SQL Query that pulls all attributes for all products.
I have the bellow code for M1 but it does not work (of cause) for M2. But I need the exact counterpart. After uploading all products into our M2 shop we want to compare for errors.
So I need: SKU, attribute_ID, attribute_text
Old M1 query:
SELECT
*
FROM
(SELECT
ce.sku,
ea.attribute_id,
ea.attribute_code,
CASE ea.backend_type WHEN 'varchar' THEN ce_varchar.value WHEN 'int' THEN ce_int.value WHEN 'text' THEN ce_text.value WHEN 'decimal' THEN ce_decimal.value WHEN 'datetime' THEN ce_datetime.value ELSE ea.backend_type END AS value,
CASE ea.backend_type WHEN 'varchar' THEN ce_varchar.store_id WHEN 'int' THEN ce_int.store_id WHEN 'text' THEN ce_text.store_id WHEN 'decimal' THEN ce_decimal.store_id WHEN 'datetime' THEN ce_datetime.store_id ELSE ea.backend_type END AS store_id,
ea.is_required AS required
FROM
catalog_product_entity AS ce
LEFT JOIN eav_attribute AS ea ON ce.entity_type_id = ea.entity_type_id
LEFT JOIN catalog_product_entity_varchar AS ce_varchar ON ce.entity_id = ce_varchar.entity_id AND ea.attribute_id = ce_varchar.attribute_id AND ea.backend_type = 'varchar'
LEFT JOIN catalog_product_entity_int AS ce_int ON ce.entity_id = ce_int.entity_id AND ea.attribute_id = ce_int.attribute_id AND ea.backend_type = 'int'
LEFT JOIN catalog_product_entity_text AS ce_text ON ce.entity_id = ce_text.entity_id AND ea.attribute_id = ce_text.attribute_id AND ea.backend_type = 'text'
LEFT JOIN catalog_product_entity_decimal AS ce_decimal ON ce.entity_id = ce_decimal.entity_id AND ea.attribute_id = ce_decimal.attribute_id AND ea.backend_type = 'decimal'
LEFT JOIN catalog_product_entity_datetime AS ce_datetime ON ce.entity_id = ce_datetime.entity_id AND ea.attribute_id = ce_datetime.attribute_id AND ea.backend_type = 'datetime'
WHERE
1=1) AS tab
WHERE
tab.value != ""
magento2 attributes sql
edited yesterday
Oshan
1,3961919
asked Jun 16 '18 at 18:54
Michael MüllerMichael Müller
3910
add a comment |
0
0
0
I need a SQL Query that pulls all attributes for all products.
I have the bellow code for M1 but it does not work (of cause) for M2. But I need the exact counterpart. After uploading all products into our M2 shop we want to compare for errors.
So I need: SKU, attribute_ID, attribute_text
Old M1 query:
SELECT
*
FROM
(SELECT
ce.sku,
ea.attribute_id,
ea.attribute_code,
CASE ea.backend_type WHEN 'varchar' THEN ce_varchar.value WHEN 'int' THEN ce_int.value WHEN 'text' THEN ce_text.value WHEN 'decimal' THEN ce_decimal.value WHEN 'datetime' THEN ce_datetime.value ELSE ea.backend_type END AS value,
CASE ea.backend_type WHEN 'varchar' THEN ce_varchar.store_id WHEN 'int' THEN ce_int.store_id WHEN 'text' THEN ce_text.store_id WHEN 'decimal' THEN ce_decimal.store_id WHEN 'datetime' THEN ce_datetime.store_id ELSE ea.backend_type END AS store_id,
ea.is_required AS required
FROM
catalog_product_entity AS ce
LEFT JOIN eav_attribute AS ea ON ce.entity_type_id = ea.entity_type_id
LEFT JOIN catalog_product_entity_varchar AS ce_varchar ON ce.entity_id = ce_varchar.entity_id AND ea.attribute_id = ce_varchar.attribute_id AND ea.backend_type = 'varchar'
LEFT JOIN catalog_product_entity_int AS ce_int ON ce.entity_id = ce_int.entity_id AND ea.attribute_id = ce_int.attribute_id AND ea.backend_type = 'int'
LEFT JOIN catalog_product_entity_text AS ce_text ON ce.entity_id = ce_text.entity_id AND ea.attribute_id = ce_text.attribute_id AND ea.backend_type = 'text'
LEFT JOIN catalog_product_entity_decimal AS ce_decimal ON ce.entity_id = ce_decimal.entity_id AND ea.attribute_id = ce_decimal.attribute_id AND ea.backend_type = 'decimal'
LEFT JOIN catalog_product_entity_datetime AS ce_datetime ON ce.entity_id = ce_datetime.entity_id AND ea.attribute_id = ce_datetime.attribute_id AND ea.backend_type = 'datetime'
WHERE
1=1) AS tab
WHERE
tab.value != ""
magento2 attributes sql
edited yesterday
Oshan
1,3961919
asked Jun 16 '18 at 18:54
Michael MüllerMichael Müller
3910
I need a SQL Query that pulls all attributes for all products.
I have the bellow code for M1 but it does not work (of cause) for M2. But I need the exact counterpart. After uploading all products into our M2 shop we want to compare for errors.
So I need: SKU, attribute_ID, attribute_text
Old M1 query:
SELECT
*
FROM
(SELECT
ce.sku,
ea.attribute_id,
ea.attribute_code,
CASE ea.backend_type WHEN 'varchar' THEN ce_varchar.value WHEN 'int' THEN ce_int.value WHEN 'text' THEN ce_text.value WHEN 'decimal' THEN ce_decimal.value WHEN 'datetime' THEN ce_datetime.value ELSE ea.backend_type END AS value,
CASE ea.backend_type WHEN 'varchar' THEN ce_varchar.store_id WHEN 'int' THEN ce_int.store_id WHEN 'text' THEN ce_text.store_id WHEN 'decimal' THEN ce_decimal.store_id WHEN 'datetime' THEN ce_datetime.store_id ELSE ea.backend_type END AS store_id,
ea.is_required AS required
FROM
catalog_product_entity AS ce
LEFT JOIN eav_attribute AS ea ON ce.entity_type_id = ea.entity_type_id
LEFT JOIN catalog_product_entity_varchar AS ce_varchar ON ce.entity_id = ce_varchar.entity_id AND ea.attribute_id = ce_varchar.attribute_id AND ea.backend_type = 'varchar'
LEFT JOIN catalog_product_entity_int AS ce_int ON ce.entity_id = ce_int.entity_id AND ea.attribute_id = ce_int.attribute_id AND ea.backend_type = 'int'
LEFT JOIN catalog_product_entity_text AS ce_text ON ce.entity_id = ce_text.entity_id AND ea.attribute_id = ce_text.attribute_id AND ea.backend_type = 'text'
LEFT JOIN catalog_product_entity_decimal AS ce_decimal ON ce.entity_id = ce_decimal.entity_id AND ea.attribute_id = ce_decimal.attribute_id AND ea.backend_type = 'decimal'
LEFT JOIN catalog_product_entity_datetime AS ce_datetime ON ce.entity_id = ce_datetime.entity_id AND ea.attribute_id = ce_datetime.attribute_id AND ea.backend_type = 'datetime'
WHERE
1=1) AS tab
WHERE
tab.value != ""
magento2 attributes sql
magento2 attributes sql
edited yesterday
Oshan
1,3961919
asked Jun 16 '18 at 18:54
Michael MüllerMichael Müller
3910
edited yesterday
Oshan
1,3961919
asked Jun 16 '18 at 18:54
Michael MüllerMichael Müller
3910
edited yesterday
Oshan
1,3961919
edited yesterday
Oshan
1,3961919
edited yesterday
Oshan
1,3961919
1,3961919
asked Jun 16 '18 at 18:54
Michael MüllerMichael Müller
3910
asked Jun 16 '18 at 18:54
Michael MüllerMichael Müller
3910
asked Jun 16 '18 at 18:54
Michael MüllerMichael Müller
3910
3910
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "479"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmagento.stackexchange.com%2fquestions%2f230209%2fmagento-2-sql-select-all-attributes-for-all-products%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Magento Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmagento.stackexchange.com%2fquestions%2f230209%2fmagento-2-sql-select-all-attributes-for-all-products%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
