Let's say I have a collection like:
$products = Mage::getModel('catalog/product')
->getCollection()
...
->load();
How do I print the actual MySQL code that gets executed?
echo $collection->load()->getSelectSql(true)
Shawn Abramson 2016-08-10 01:54
You can always view your sql query at a certain point by echoing getSelect as shown:
$products = Mage::getModel('catalog/product')
->getCollection();
echo $products->getSelect();
To change query parameters you want to check out methods like:
$products->addAttributeToSelect('someattribute');
$products->addAttributeToFilter('someattribute', array('eq'=>'1'));
echo $collection->load()->getSelect()
. Its cleaner to use echo $collection->getSelectSql(true)
Shawn Abramson 2016-05-15 07:13
->load()
before running $collection->getSelectSql(true). The attributes get added to the query when the collection is loaded - Shawn Abramson 2016-08-10 01:53
You can print collection using below code:
We can print query of collection using getSelect()->__toString()
$products = Mage::getModel(‘catalog/product’)
->addAttributeToFilter(‘status’, array(‘eq’ => 1));
echo $products->getSelect()->__toString();
Have you seen http://kuldipchudasama.wordpress.com/2012/07/16/magento-print-query-of-collection/? This works well.
Most other answers here say that $products->getSelect()
will do it - this is fine if all you're going to do with it is echo
, but in fact getSelect()
doesn't just return a string, it returns a Varien_Db_Select object.
Invoking echo
on that object automatically triggers its __toString()
method, so you just get the SQL string, but try passing it to Mage::log()
and you'll get a lot more than you expected.
If you just want to log the SQL, you can use:
Mage::log($products->getSelect()->__toString());
Or how about using the object's own:
$products->printLogQuery(false, true); // don't echo, do log
printLogQuery
is defined in lib/Varien/Data/Collection/Db.php.
You can print
$products->getSelect()->assemble();
assemble()
, just echo $products->getSelect();
Max 2012-04-04 16:44
If you simple set the first parameter of ->load()
to true
, like so:
$products = Mage::getModel('catalog/product')
->getCollection()
...
->load(true);
I work with collections every day. This is without a doubt the correct way.
echo $collection->getSelectSql(true);
Step 1-
$result_colletion = print_r($collection->getSelect());
Mage::log($$result_colletion, null, custom_collection.log,true);
Step 2-
After that Login into magento admin section and enable to log setting . Please see below .
System > Configuration > Developer > Log Settings
Step 3-
After that see the log file custom_collection.log
in var/log/
folder .
In Magento 2:-
namespace <Company>\<Module>\Block\Adminhtml\Tab\Log;
class Grid
extends \Magento\Backend\Block\Widget\Grid\Extended
{
protected $_collectionFactory;
/**
* Constructor
*
* @param \Magento\Backend\Block\Template\Context $context
* @param \Magento\Backend\Helper\Data $backendHelper
* @param \<Company>\<Module>\Model\ResourceModel\Log\CollectionFactory $collectionFactory
* @param Psr\Log\LoggerInterface $logger
* @param array $data
*/
public function __construct(
\Magento\Backend\Block\Template\Context $context,
\<Company>\<Module>\Model\ResourceModel\Log\CollectionFactory $collectionFactory,
\Psr\Log\LoggerInterface $logger,
array $data = []
) {
$this->_logger = $logger;
$this->_collectionFactory = $collectionFactory;
parent::__construct($context, $backendHelper, $data);
}
/**
* {@inheritdoc}
*/
protected function _prepareCollection()
{
$collection = $this->_collectionFactory->create();
$this->_logger->info($collection->getSelect()->__toString());
$this->setCollection($collection);
return parent::_prepareCollection();
}
}
And remember that the collection factory is a magic class that can attaches to every class as Magento 1 wasn't complicated enough.
Try following code.
$products = Mage::getModel('catalog/product')
->getCollection();
echo $products->getSelect();
$collection = "Sql Query Here";
echo $collection->getSelect()->__toString();
Sql Query Products Catalog
select e.entity_id, IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS name, pcategory_name.value AS 'PCat', eaov.value AS 'brand', stoke.stock_status AS 'inStoke', pcategory_name.entity_id AS 'cat_id', ccei.value AS 'is_active' FROM catalog_product_entity AS e INNER JOIN catalog_product_entity_varchar AS at_name_default ON (at_name_default.entity_id = e.entity_id) AND (at_name_default.attribute_id = (SELECT attribute_id FROM eav_attribute ea LEFT JOIN eav_entity_type et ON ea.entity_type_id = et.entity_type_id WHERE ea.attribute_code = 'name' AND et.entity_type_code = 'catalog_product')) AND at_name_default.store_id = 0 LEFT JOIN catalog_product_entity_varchar AS at_name ON (at_name.entity_id = e.entity_id) AND (at_name.attribute_id = (SELECT attribute_id FROM eav_attribute ea LEFT JOIN eav_entity_type et ON ea.entity_type_id = et.entity_type_id WHERE ea.attribute_code = 'name' AND et.entity_type_code = 'catalog_product')) AND (at_name.store_id = 1) LEFT JOIN cataloginventory_stock_status AS stoke ON (stoke.product_id = e.entity_id) LEFT JOIN catalog_product_entity_varchar AS key ON (key.entity_id = e.entity_id) AND key.attribute_id = 160 INNER JOIN catalog_category_entity AS pcategory ON pcategory.entity_id = (SELECT MAX(category_id) FROM catalog_category_product WHERE product_id = e.entity_id AND category_id != 2) INNER JOIN catalog_category_entity_int AS ccei ON ccei.entity_id=pcategory.entity_id AND ccei.attribute_id=34 LEFT JOIN catalog_category_entity_varchar AS pcategory_name ON pcategory_name.entity_id = pcategory.entity_id AND pcategory_name.attribute_id = 33 LEFT JOIN catalog_product_entity_int AS cpei ON cpei.entity_id = e.entity_id AND cpei.entity_type_id = 4 AND cpei.attribute_id = 70 LEFT JOIN catalog_product_entity_int AS cpeis ON cpeis.entity_id = e.entity_id AND cpeis.attribute_id = 155 LEFT JOIN eav_attribute_option_value AS eaov ON eaov.option_id = cpei.value WHERE ccei.value=1 AND pcategory.parent_id=2
echo $collection->getSelectSql(true);
Shawn Abramson 2016-05-15 07:11