Tag: Database

Posted on

How to use insertOnDuplicate query in Magento 2.

Use of insertOnDuplicate query using Magento 2,  You need to create a Connection object first to run the query using ResourceConnection class.

When you insert any new row into a table if the row causes a duplicate in the Primary key or UNIQUE index, throw an error in MySQL.

Base Definition of function:

/**
 * Inserts a table row with specified data.
 *
 * @param mixed $table The table to insert data into.
 * @param array $data Column-value pairs or array of column-value pairs.
 * @param array $fields update fields pairs or values
 * @return int The number of affected rows.
 */
public function insertOnDuplicate($table, array $data, array $fields = []);

Example,
Continue reading “How to use insertOnDuplicate query in Magento 2.”

Posted on

Rename a tablename using db_schema(Declarative schema) in Magento 2.

In Magento 2, You can create a new table using db_schema.xml file. After creating a table you want to rename a table name, You can rename a table using declarative schema.

New Table(mycustom_table) db_schema.xml file,

<?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="mycustom_table" onCreate="migrateDataFromAnotherTable(new_declarative_table)">
        <column xsi:type="int" name="entity_id" padding="10" unsigned="true" nullable="false" identity="true"
                comment="Entity Id"/>
        <column xsi:type="varchar" name="fullname" nullable="true" length="50" comment="Full name"/>
        <column xsi:type="varchar" name="email" nullable="true" length="255" comment="Email"/>
        <column xsi:type="smallint" name="store_id" padding="5" unsigned="true" nullable="true" identity="false"
                default="0" comment="Store Id"/>
    </table>
</schema>

Now you must have to run generate-whitelist command to generate db_schema_whitelist.json file, Continue reading “Rename a tablename using db_schema(Declarative schema) in Magento 2.”

Posted on

Drop a column from the table using db_schema.xml in Magento 2.

In Magento 2, You can create a new table using db_schema.xml file. The table contains many columns based on your requirements.

Check the blog for, How to creates a new database table.

In the future, if you want to remove or drop some column from an existing table you can simply drop a column using db_schema.xml file.

Let’s assume, You have created a table name, my_custom_table in the database with email column and you want to remove email column.

Table with a list of entry and some of the columns you don’t require, You can remove those columns using attribute disabled=true in a db_schema.xml file. Continue reading “Drop a column from the table using db_schema.xml in Magento 2.”