mysql - Mysql2::Error: View's SELECT contains a subquery in the FROM clause while running rails migration -

using rails migration (but doesn't matter). trying create view here, got

mysql2::error: view's select contains subquery in clause: create view `lanciao` 

when try this:

create view lanciao   select     subtime(payment.created_at, '0 5:0:0') transaction_date,     `order_item`.total_quantity,     payment.amount amount,     `order`.payment_type   payments payment   join orders `order` on `order`.id = payment.order_id   left join donation_items donation on donation.order_id = `order`.id   inner join (     select order_id, sum(quantity) total_quantity      order_items     group order_id   ) `order_item` on `order`.id = `order_item`.order_id   payment.payment_state in ('settled', 'completed', 'captured', 'authorized', 'approved')   , `order`.fixed_order_id null 

without create view lanciao, can run select block end fine. tried refactor this, couldn't working. how should fix this?

am aware of separating different views , make reference 1 another, wouldn't prefer way.

the mysql docs state:

before mysql 5.7.7, select statement cannot contain subquery in clause.

if can upgrade mysql 5.7.7+ should able you're trying do.

otherwise might want try creating view of subquery , using in from.

so instead sql may like:

create view subquery_view   select order_id, sum(quantity) total_quantity    order_items   group order_id  create view lanciao   select     subtime(payment.created_at, '0 5:0:0') transaction_date,     `order_item`.total_quantity,     payment.amount amount,     `order`.payment_type   payments payment   join orders `order` on `order`.id = payment.order_id   left join donation_items donation on donation.order_id = `order`.id   inner join subquery_view `order_item` on `order`.id = `order_item`.order_id   payment.payment_state in ('settled', 'completed', 'captured', 'authorized', 'approved')   , `order`.fixed_order_id null 

be aware nesting views inside each other can cause performance issues i'm unaware of alternative approach.