Getting report of products through database query (EAV)

Question

I am trying to learn more about EAV and I was doing some testing on a magento 2 sample data database.

I just want to retrieve the the product id and the description of the product but there is a mismatch with the total amount of products in the catalog_product_entity table and the catalog_product_entity_text table:

There are 2046 products in the catalog_product_entity table:

If I use the following query I get 2052 results:

SELECT product.entity_id as "Description",description.value FROM catalog_product_entity_text description,catalog_product_entity product where product.entity_id = description.entity_id ORDER BY product.entity_id

Answer 1

I assume there are a few cases where an entity_id matches more than one row in the text table. Perhaps there can be a text attribute other than description?

Try this:

SELECT product.entity_id as Product_id,
  COUNT(*) AS count,
  GROUP_CONCAT(description.value) AS Description
FROM catalog_product_entity_text description
LEFT OUTER JOIN catalog_product_entity product ON product.entity_id = description.entity_id 
GROUP BY product.entity_id
ORDER BY product.entity_id

I am not familiar with Magento's EAV tables, but I assume the table should have a column for the attribute identifier as well as an entity_id. You may have to filter based on the attribute type, if you just want the Description, and not other text attributes.

P.S.: I adapted your query to use modern JOIN syntax. You shouldn't use the "comma-style" joins, they went out of style in 1992.