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







share|improve this question





























    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







    share|improve this question



























      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







      share|improve this question















      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






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited yesterday









      Oshan

      1,3961919




      1,3961919










      asked Jun 16 '18 at 18:54









      Michael MüllerMichael Müller

      3910




      3910






















          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


















          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














          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





















































          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







          -

          Popular posts from this blog

          An IMO inspired problem

          Image
          14 4 This problem from IMO 1988 is said to be one of the most elegant ones in functional equations. Problem : The function $f$ is defined on the set of all positive integers as follows: begin{align} f(1) = 1, f(3) &= 3, f(2n) = f(n), \ f(4n+1) &= 2f(2n+1) - f(n), \ f(4n+3) &= 3 f(2n+1) - 2f(n) end{align} Find the number of $n$ with $f(n) = n, 1 leq n leq 1988$. The main idea towards the solution is realizing that these conditions stand for the fact that $f(n)$ just reverses the digits in the binary representation of the number $n$. So, essentially the solution is finding the number of binary pallindromes $leq 1988_{10}$. My question is the following : How to reformulate the problem so that $f(n)$ reverses the digits of $n$ in its ternary representation. Or even better, can we reformulate it for a
          Read more

          Management

          Image
          Management From Wikipedia, the free encyclopedia Jump to navigation Jump to search "Manager" redirects here. For other uses, see Management (disambiguation) and Manager (disambiguation). An organization chart for the United States Coast Guard shows the hierarchy of managerial roles in that organization. Business administration Management of a business Accounting Management accounting Financial accounting Financial audit Business entities Cooperative Corporation Limited liability company Partnership Sole proprietorship State-owned enterprise Corporate governance Annual general meeting Board of directors Supervisory board Advisory board Audit committee Corporate law Commercial law Constitutional documents Contract Corporate crime Corporate liability Insolvency law International trade law Mergers and acquisitions Economics Commodity Pu
          Read more

          Has there ever been an instance of an active nuclear power plant within or near a war zone?

          Image
          36 3 The question Has there ever been a military combat going on around an active (running or in outage but not decommissioned) commercial nuclear power plant? Or dangerously near it? If yes, how did the plant and its personnel fare in such situation? Why I ask Let's assume for the scope of this question modern commercial nuclear power plants (CANDU, PWR, BWR of generation II plus all of gen III and newer) are usually reasonably safe to operate given that the engineering has been done right and there is a whole cohort of very well trained personnel on-site at all times, well rested and with considerable resources on their hands. Things hard to get with an armed conflict like the recent Syrian war raging around. I can't seem to find any reference or a comprehensive article about this, hence this question.
          Read more