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
Best Answer
Magento itself in the rather newish inventory project creates the
inventory_stock_*
tables itself via plain 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 :-(