MySQL database query logging can be enabled/disabled from command line. This will help in debugging database queries. Enable database query logging The following CLI command enabled database/mysql query log: bin/magento dev:query-log:enable Flush cache bin/magento cache:flush After that, browse your Magento site. The database queries logs are saved in the file var/debug/db.log. Note: The database query … Read more
This article shows how you can insert multiple rows or records into your database table. Generally, we add a single row of data into the database table. However, we have situations where we need to insert multiple rows of data into the table at once. We can do so by running the insert query multiple … Read more
This article shows how to write/run/execute custom SQL queries in Magento 2. We need to instantiate class Magento\Framework\App\ResourceConnection for this. You have to inject this resource class in your module’s Block/Model/Controller class constructor. After that, you can use that object to run custom sql queries. In this example, I will simply be using object manager … Read more
This article shows SQL query to get size (in MB) of all MySQL databases. It also shows the query to get the size (in MB) of all the tables present in any particular database. Get size (in MB) of all MySQL databases SELECT table_schema "Database Name", sum( data_length + index_length ) / 1024 / 1024 … Read more
Here is a quick tip to add OR query and AND query to collection object. I am implementing this on product collection. Getting product collection // PRODUCT COLLECTION $collection = Mage::getModel('catalog/product')->getCollection(); Adding AND query condition Filtering collection to select only those products whose sku is like ‘ch’ AND status is equal to 1 (i.e. enabled … Read more
In my previous article (Magento: Very Useful Collection Functions), I had written about database interaction functions present in class Varien_Data_Collection_Db. Here, I am going to explain some database interaction functions present in the class Mage_Eav_Model_Entity_Collection_Abstract. These collection functions are very useful to select data from Magento database. We need them almost all the time for … Read more
Scenario: You have created a custom module. You have entered certain data in your database. You need to show the data randomly. Solution: In MySQL the rand() function helps the select query to fetch data randomly. In Magento, you can select random rows from MySQL table using Zend_Db_Expr(‘RAND()’). You have to create a new function … Read more
The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables. [@via w3schools] A SQL JOIN clause combines records from two or more tables in a database. It creates a set that can be saved as a table or … Read more