1 year ago

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

Posted 1 year ago by sbkl


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 
                    product_variations.product_id AS product_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 (
                        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 (
                        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.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.