Why product collection model don't show real Sql query?

Question

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?

Answer 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());

Answer 2

Try following code:

error_log($collection->getSelectSql(true));