2

Is it possible to create a mysql view in Magento 2 just like we create table in InstallScheme.php. I want to create a view which will be a join of multiple table with selected columns.

1

This is possible, but unfortunately, it requires you to use plain SQL. At least this is how Magento itself does it in the rather newish inventory project (see https://github.com/magento/inventory/blob/40e199b82fd2e1cdd07541f856aa45a8a2e40fc3/InventoryCatalog/Setup/Patch/Schema/CreateLegacyStockStatusView.php#L68-L83):

$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);

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

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.