Multiple SQL queries in Magento 2 can't be executed

Question

I have a custom module and i want to define a Mysql Function in Setup/InstallSchema.php:

    $query = 'DELIMITER $$
        DROP FUNCTION IF EXISTS getQuantity;
        CREATE FUNCTION getQuantity(PID INT) RETURNS INT
        BEGIN DECLARE sum_qty INT;
        SELECT sum(qty) INTO sum_qty FROM
        (SELECT qty FROM cataloginventory_stock_item WHERE product_id = PID
            UNION ALL
            (SELECT IFNULL(sum(qty),0) as qty FROM cataloginventory_stock_item
                WHERE product_id IN (SELECT product_id FROM catalog_product_super_link WHERE parent_id = PID))) qty;
        RETURN sum_qty;
        END;
        $$ DELIMITER;';

    $installer->getConnection()->query($query);

When run setup:upgrade, it throws an exception:

Multiple queries can't be executed. Run a single query and try again.

How can I fix it? The function \Magento\Framework\DB\Adapter\Pdo\MySql::multiQuery() is deprecated and I don't want to use it.

Answer 1

You can use like.

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

you can get reference from core magento file "vendor/magento/module-inventory-catalog/Setup/Patch/Schema/CreateLegacyStockStatusView.php" .

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