Magento 2 – debug collection filter
Sometimes it is quite complicated to debug collection filters. You may have the correct code, but it doesn’t return data. Hours later you find out, that there are no data available for this filter. To find a fast an simple solution, I show you how to fast debug collections and collection filters.
Magento 2 – debug collection filter
With Magento collections you can build very complex filters. You can join tables, order them an set a long where clause. In Magento this is done without any SQL knowledge. That may sounds good, but in fact can be a problem if you want to change an existing filter or build a own one. Most developers have good SQL knowledge. If you got stuck with a filter and do not know which sub array is now an AND or an OR, you can print out SQL statement which is generated in background. Each collection (and filter) is translated into one SQL statement. You can do the following:
1 2 3 4 5 | $col = $this->_productCollectionFactory->create() ->addFieldToFilter('ean', $ean) ->load(); echo $col->getSelect()->__toString(); |
You can also put a die(); at the end if you got such a collection for a adminhtml site. This will immediately stop this script and prints out corresponding SQL statement. Now you can copy past it and run it in your preferred SQL front end. You now see generated return or error messages. Magento often wraps those SQL errors and you are wondering why you don’t get any result.
Testing
My example created following SQL statement:
1 2 3 4 5 | SELECT `e`.*, `at_ean`.`value` AS `ean`, `stock_status_index`.`stock_status` AS `is_salable` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_product_entity_varchar` AS `at_ean` ON (`at_ean`.`entity_id` = `e`.`entity_id`) AND (`at_ean`.`attribute_id` = '136') AND (`at_ean`.`store_id` = 0) LEFT JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1 WHERE (at_ean.value = '4002512113069') |
It turns out, that it was a data error. The requested EAN number was missing because of a previous if. So in this case, there was no error with my statement.
Conclusion
You can debug collection filter by echoing SQL statements. With this statement you can run requests directly to database and get errors or response data. You can also adept this SQL statement to your needs and then manipulate Magento filter code till you get the same statement. Most of the time this technique is faster in development as writing complex join filters in Magento, especially if you do not know exactly how it works. I have also a concrete idea of a needed SQL statement, but have no idea how this may look in Magento code.
Thanks for sharing about testing Magento collection filter.