InsideDarkWeb.com

Magento 2 : create view table via setup script

How do you create a view table via setup script

Direct sql CREATE OR REPLACE VIEW... etc

Or is there a magento way?

Update

Thanks for your suggestions so far. I’m asking whether possible to create a view table.

To clarify I know how to create a standard table.

Magento 2.2 (EOL) syntax would be as follows

<?php

namespace VendorExampleSetup;

use MagentoFrameworkSetupModuleContextInterface;
use MagentoFrameworkSetupSchemaSetupInterface;
use MagentoFrameworkSetupInstallSchemaInterface;

class InstallSchema implements InstallSchemaInterface
{

    /**
     * {@inheritdoc}
     */
    public function install(
        SchemaSetupInterface $setup,
        ModuleContextInterface $context
    ) {
        $table_vendor_example_example = $setup->getConnection()->newTable($setup->getTable('vendor_example_example'));

        $table_vendor_example_example->addColumn(
            'example_id',
            MagentoFrameworkDBDdlTable::TYPE_INTEGER,
            null,
            ['identity' => true,'nullable' => false,'primary' => true,'unsigned' => true,],
            'Entity ID'
        );

        $table_vendor_example_example->addColumn(
            'title',
            MagentoFrameworkDBDdlTable::TYPE_TEXT,
            255,
            [],
            'Title'
        );

        $table_vendor_example_example->addColumn(
            'content',
            MagentoFrameworkDBDdlTable::TYPE_TEXT,
            null,
            [],
            'content'
        );

        $table_vendor_example_example->addColumn(
            'status',
            MagentoFrameworkDBDdlTable::TYPE_SMALLINT,
            null,
            [],
            'Status'
        );

        $setup->getConnection()->createTable($table_vendor_example_example);
    }
}

2.3 syntax would look a little like this

etc/db_schema.xml

<?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 comment="vendor_example_example Table" engine="innodb" name="vendor_example_example" resource="default">
        <column comment="Entity Id" identity="true" name="example_id" nullable="false" padding="6" unsigned="false" xsi:type="smallint"/>
        <constraint referenceId="PRIMARY" xsi:type="primary">
            <column name="example_id" comment="ID"/>
        </constraint>
        <column length="255" name="title" nullable="true" xsi:type="varchar" comment="Title"/>
        <column name="content" nullable="true" xsi:type="text" comment="Content"/>
        <column name="status" nullable="true" xsi:type="smallint" comment="Status"/>
    </table>
</schema>

I’m asking how to create a view table. Or more accurately a database view.

View tables are used as part of Magento 2.3 MSI – see inventory_stock_1

Magento Asked by Dominic Xigen on February 15, 2021

2 Answers

2 Answers

Magento itself in the rather newish inventory project creates the inventory_stock_* tables itself via plain SQL:

$sql = "CREATE
        SQL SECURITY INVOKER
        VIEW {$viewToLegacyIndex}
          AS
            SELECT
            DISTINCT    
              legacy_stock_status.product_id,
              legacy_stock_status.website_id,
              legacy_stock_status.stock_id,
              legacy_stock_status.qty quantity,
              legacy_stock_status.stock_status is_salable,
              product.sku
            FROM {$legacyStockStatusTable} legacy_stock_status
              INNER JOIN {$productTable} product
                ON legacy_stock_status.product_id = product.entity_id;";
$this->schemaSetup->getConnection()->query($sql);

See https://github.com/magento/inventory/blob/40e199b82fd2e1cdd07541f856aa45a8a2e40fc3/InventoryCatalog/Setup/Patch/Schema/CreateLegacyStockStatusView.php#L68-L83.

Hence, I doubt that there is a better way in Magento to create views :-(

Correct answer by Simon on February 15, 2021

If you want to create SQL table using magento 2 script then follow the below code.

you need to create InstallSchema.php file at given path.

VendorExampleSetup;

namespace VendorExampleSetup;

use MagentoFrameworkSetupInstallSchemaInterface;
use MagentoFrameworkSetupModuleContextInterface;
use MagentoFrameworkSetupSchemaSetupInterface;

class InstallSchema implements InstallSchemaInterface
{

    public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        $installer = $this;

        $installer->startSetup();

        /**
        * create lesson05 table
        */
        $installer->run("
        DROP TABLE IF EXISTS {$this->getTable('example')};
        CREATE TABLE {$this->getTable('example')} (
        `example_id` int(11) unsigned NOT NULL auto_increment,
        `title` varchar(255) NOT NULL default '',
        `content` text NOT NULL default '',
        `status` smallint(6) NOT NULL default '0',
        PRIMARY KEY (`lesson05_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

        INSERT INTO `{$this->getTable('example')}` VALUES (1,'Magento Course','Hello, I am Your Friend from India',1);
        ");
        $installer->endSetup();
    }
}

Answered by Bharat Desai - Certified Dev on February 15, 2021

Add your own answers!

Related Questions

Get userid of the admin logged in magento 2 helper

1  Asked on February 12, 2021 by fahad-bashir

   

Product saving problem in admin control panel

1  Asked on February 12, 2021 by venkatesha

 

Print Address / Shipping Label For Magento 2

0  Asked on February 12, 2021 by naveen-sama

     

are the two codes same?

1  Asked on February 11, 2021 by hong-thng

   

Create custom Attributes in Customer Form in magento 1.9

1  Asked on February 6, 2021 by maiitsyuj

 

Magento2 Language Character Problem

0  Asked on February 5, 2021 by stifboy

   

Magento 1.9.2.4 Add Cart To Header

2  Asked on February 5, 2021 by ks6814

         

Ask a Question

Get help from others!

© 2021 InsideDarkWeb.com. All rights reserved.