2

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 Vendor\Example\Setup;

use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\InstallSchemaInterface;

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',
            \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
            null,
            ['identity' => true,'nullable' => false,'primary' => true,'unsigned' => true,],
            'Entity ID'
        );

        $table_vendor_example_example->addColumn(
            'title',
            \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
            255,
            [],
            'Title'
        );

        $table_vendor_example_example->addColumn(
            'content',
            \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
            null,
            [],
            'content'
        );

        $table_vendor_example_example->addColumn(
            'status',
            \Magento\Framework\DB\Ddl\Table::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

  • There is no view concept in Magento regarding Mysql DBs. You can use EAV Models insted. – vignesh Sep 28 '19 at 9:25
  • 1
    @vignesh 2.3 MSI feature uses view tables – Dominic Xigen Sep 28 '19 at 12:28
1

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 :-(

| improve this answer | |
-1

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.

Vendor\Example\Setup;

namespace Vendor\Example\Setup;

use Magento\Framework\Setup\InstallSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;

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();
    }
}
| improve this answer | |
  • 1
    Thanks for your answer. Although I wanted to know if there was a magento way to create a view table. I've added more detail in my question. – Dominic Xigen Sep 28 '19 at 12:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.