Write a fetchCol() Sql query in Magento 2.

Write a Mysql fetchCol() query using Magento standard way without using the Model file.

To Fetches the first column of all SQL result rows as an array.

Return Type: fetchCol() always returns as an array. Continue reading “Write a fetchCol() Sql query in Magento 2.”

Write a Delete SQL query statement in Magento 2.

Write a MySQL delete query using Magento’s standard way of deleting specific rows from the database table by Magento.

You can write direct SQL query delete() without worrying about Model operation using a given code snippet in the blog. Continue reading “Write a Delete SQL query statement in Magento 2.”

How to add timestamp column in sales_order_item table using db_schema.xml Magento 2?

You can add a new column, a field in sales_order_item table using db_schema.xml under your module, etc folder from Magento 2.3 and higher version.

We try to add a new column called item_expiry_date in sales_order_item table.

To add item_expiry_date field in table you need to only add one row inside the db_schema.xml file in your module.

Here, the Main table for Sales Item Operation is “sales_order_item” and you can add a new field in the table using below simple code snippet,

<?xml version="1.0"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="sales_order_item" resource="sales" engine="innodb" comment="Sales Order Item">
        <column xsi:type="timestamp" name="item_expiry_date" on_update="false" nullable="true" comment="Item expiry date"/>
    </table>
</schema>

Here New Column type is a timestamp. You can add timestamp at item level using adding a new column in sales_order_item table.

Run command from Magento root instance,
php bin/magento setup:upgrade

Check the sales_order_item table in Database and one new column is added to the table and you can see new column.