sbkl
103
3
Laravel

Laravel migration: Drop View with DB::statement doesn't work

Posted 4 weeks ago by sbkl

Hi,

One of my migration is creating a dynamic view calculating stock. This is the DB statement in my Up function:

public function up()
    {
        DB::statement("CREATE VIEW product_variation_stock_view AS 
                SELECT 
                    product_variations.product_id AS product_id,
                    product_variations.id AS product_variation_id,
                    COALESCE(SUM(stocks.quantity) - COALESCE(SUM(product_variation_order.quantity),0),0) as stock,
                    CASE WHEN COALESCE(SUM(stocks.quantity) - COALESCE(SUM(product_variation_order.quantity),0),0) > 0
                        then true
                        else false
                    END in_stock
                FROM product_variations                
                LEFT JOIN (
                    SELECT
                        stocks.product_variation_id AS id,
                        SUM(stocks.quantity) as quantity
                    FROM stocks
                    GROUP BY stocks.product_variation_id
                ) AS stocks USING(id)
                LEFT JOIN (
                    SELECT
                        product_variation_order.product_variation_id AS id,
                        SUM(product_variation_order.quantity) AS quantity
                    FROM product_variation_order
                    GROUP BY product_variation_order.product_variation_id
                ) AS product_variation_order USING(id)
                GROUP BY product_variations.id, product_variations.product_id
        ");
    }

This is working great.

Here is the down function:

    public function down()
    {
        DB::statement("DROP VIEW IF EXISTS product_variation_stock_view");
    }

When I run the query inside Sequel Pro, this is working well.

But when I run the migrate:fresh command, Laravel just ignore it and therefore the table is not dropped before to attempt to create this view again and fail because it already exists.

Any idea what I am doing wrong for the down function to work?

Thank you.

Please sign in or create an account to participate in this conversation.

Reply to

Use Markdown with GitHub-flavored code blocks.