0

I want to understand how M2 loads products EAV attributes, so I wrote the following code:

        $productCollection = $this->productCollection->addFilter('entity_id', 7)
            ->addAttributeToSelect('*')->load();

        $sql = $productCollection->getSelect()->__toString();

In code above $this->productCollection is Magento\Catalog\Model\ResourceModel\Product\Collection

When I debug the above code $sql get value SELECT e.* FROM catalog_product_entity AS e WHERE (entity_id=7)

Note: I get all product attributes related to product with entity_id 7, these attributes don't exist in catalog_product_entity table, I was surprised, because don't see any joins to tables catalog_product_entity_int, catalog_product_entity_decimal, catalog_product_entity_varchar, catalog_product_entity_datetime, catalog_product_entity_text

When enabled MySQL query logging, I have seen:

SELECT `t_d`.`attribute_id`, `e`.`entity_id`, `t_d`.`value` AS `default_value`, `t_s`.`value` AS `store_value`, IF(t_s.value_id IS NULL, t_d.value, t_s.value) AS `value`
FROM `catalog_product_entity_int` AS `t_d`
INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = t_d.entity_id
LEFT JOIN `catalog_product_entity_int` AS `t_s` ON t_s.attribute_id = t_d.attribute_id AND t_s.entity_id = t_d.entity_id AND t_s.store_id = 1
WHERE (e.entity_id IN (7)) AND (t_d.attribute_id IN ('93', '139', '141', '144', '126', '123', '83', '91', '130', '133', '115', '134', '131', '97', '129', '99', '132')) AND (t_d.store_id = IFNULL(t_s.store_id, 0))
UNION ALL SELECT `t_d`.`attribute_id`, `e`.`entity_id`, `t_d`.`value` AS `default_value`, `t_s`.`value` AS `store_value`, IF(t_s.value_id IS NULL, t_d.value, t_s.value) AS `value`
FROM `catalog_product_entity_decimal` AS `t_d`
INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = t_d.entity_id
LEFT JOIN `catalog_product_entity_decimal` AS `t_s` ON t_s.attribute_id = t_d.attribute_id AND t_s.entity_id = t_d.entity_id AND t_s.store_id = 1
WHERE (e.entity_id IN (7)) AND (t_d.attribute_id IN ('81', '98', '117', '77', '78', '92', '137', '135', '136', '82')) AND (t_d.store_id = IFNULL(t_s.store_id, 0))
UNION ALL SELECT `t_d`.`attribute_id`, `e`.`entity_id`, `t_d`.`value` AS `default_value`,`t_s`.`value` AS `store_value`, IF(t_s.value_id IS NULL, t_d.value, t_s.value) AS `value`
FROM `catalog_product_entity_varchar` AS `t_d`
INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = t_d.entity_id
LEFT JOIN `catalog_product_entity_varchar` AS `t_s` ON t_s.attribute_id = t_d.attribute_id AND t_s.entity_id = t_d.entity_id AND t_s.store_id = 1
WHERE (e.entity_id IN (7)) AND (t_d.attribute_id IN ('114', '100', '116', '140', '143', '96', '127', '87', '109', '125', '86', '84', '118', '73', '106', '104', '124', '88', '110', '169', '128', '89', '111', '121', '122', '142')) AND (t_d.store_id = IFNULL(t_s.store_id, 0))
UNION ALL SELECT `t_d`.`attribute_id`, `e`.`entity_id`, `t_d`.`value` AS `default_value`, `t_s`.`value` AS `store_value`, IF(t_s.value_id IS NULL, t_d.value, t_s.value) AS `value` FROM `catalog_product_entity_datetime` AS `t_d`
INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = t_d.entity_id
LEFT JOIN `catalog_product_entity_datetime` AS `t_s` ON t_s.attribute_id = t_d.attribute_id AND t_s.entity_id = t_d.entity_id AND t_s.store_id = 1
WHERE (e.entity_id IN (7)) AND (t_d.attribute_id IN ('101', '102', '94', '95', '79', '80')) AND (t_d.store_id = IFNULL(t_s.store_id, 0))
UNION ALL SELECT `t_d`.`attribute_id`, `e`.`entity_id`, `t_d`.`value` AS `default_value`, `t_s`.`value` AS `store_value`, IF(t_s.value_id IS NULL, t_d.value, t_s.value) AS `value`
FROM `catalog_product_entity_text` AS `t_d`
INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = t_d.entity_id
LEFT JOIN `catalog_product_entity_text` AS `t_s` ON t_s.attribute_id = t_d.attribute_id AND t_s.entity_id = t_d.entity_id AND t_s.store_id = 1
WHERE (e.entity_id IN (7)) AND (t_d.attribute_id IN ('103', '75', '138', '85', '76')) AND (t_d.store_id = IFNULL(t_s.store_id, 0))

I believe that this is a real query that was executed to DB.

My question is why $productCollection->getSelect()->__toString() is not returning real select that was processed to DB?

1

Collections will assemble the complete SQL query in _beforeLoad() method. This means you will get a partial query (or even an empty string) if you try to output the SQL query before loading the collection.

To get the complete SQL query like the one executed in database, you will have to load the collection first (the load() method will execute the _beforeLoad()).

$collection->load();

// the following statements are equivalent
$collection->getSelect()->assemble();
$collection->getSelect()->__toString();
echo $collection->getSelect(); // will call magic method __toString() behind the scenes which in turn calls assemble()

=== Update ===

If you see how entity based model loaded, you will see that entity collection attributes are loaded in separate query after entity collection is loaded. Entity collection is loaded here, and entity collection attributes are loaded here. So when you try to get the entity collection query using $collection->getSelect()->assemble(), you will only get the entity collection query without the entity collection attributes query.

Therefore, to debug the attributes query, you can add below code after this line:

dump($select->assemble());
  • If you take a look again at my question, then you'll see that I loaded collection before call __toString() – underser Sep 19 at 14:11
  • check updated answer. – Rendy Eko Prastiyo Sep 19 at 18:03
  • Thank you, for the explanation. – underser Sep 20 at 8:26
0

Try following code:

error_log($collection->getSelectSql(true));
  • The \Magento\Framework\Data\Collection\AbstractDb::getSelect don't have any arguments, and this wouldn't help – underser Sep 19 at 14:14

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.