Articles tagged with: query
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 to instantiate the resource class.
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
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
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 filtering collection object.
You have created a custom module.
You have entered certain data in your database.
You need to show the data randomly.
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 used as is. A JOIN is a means for combining fields from two tables by using values common to each. ANSI standard SQL specifies four types of JOINs: INNER, OUTER, LEFT, and RIGHT. In special cases, …
Cannot insert the value NULL into column ‘SourceId’, table ‘Advisory.dbo.AdvDocSource’; column does not allow nulls. INSERT fails.
The statement has been terminated. The ‘CompanyAdd’ procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
This error occurs because the ‘SourceId’ column in my table is primary key and it is set to not null. i.e. it doesn’t allow null entries. And I have not made it auto increment.