Ça fais bien longtemps que je suis plus venu ici, depuis que j'ai perdu le gout du PHP... (La je deviens fan de Ruby On Rails)
Mais la j'ai un gros problème:
J'ai une query SQL écrite pour PgSQL. Est-ce que quelqu'un pourrais m'écrire un equivalent pour MySQL? J'ai essayé mais mes connaisances ne sont pas assez bonnes pour ça...
Le code:
Code
select (select count(*)
from orders
where status = 'C' and
lower(payment_type) != 'free' and
current_date - #{days} <= order_time) as orders,
sum(line_items.unit_price * quantity)
- sum(coalesce(coupons.amount, 0))
- sum(line_items.unit_price * quantity * coalesce(percentage, 0) / 100) as earned,
sum(quantity) as q,
products.name as product
from orders
inner join line_items on orders.id = line_items.order_id
left outer join products on products.id = line_items.product_id
left outer join coupons on coupons.id = orders.coupon_id
where status = 'C' and lower(payment_type) != 'free' and current_date - #{days} <= order_time
group by product
from orders
where status = 'C' and
lower(payment_type) != 'free' and
current_date - #{days} <= order_time) as orders,
sum(line_items.unit_price * quantity)
- sum(coalesce(coupons.amount, 0))
- sum(line_items.unit_price * quantity * coalesce(percentage, 0) / 100) as earned,
sum(quantity) as q,
products.name as product
from orders
inner join line_items on orders.id = line_items.order_id
left outer join products on products.id = line_items.product_id
left outer join coupons on coupons.id = orders.coupon_id
where status = 'C' and lower(payment_type) != 'free' and current_date - #{days} <= order_time
group by product
Code
select date_part('year', orders.order_time) as year,
date_part('month', orders.order_time) as month,
date_part('day', orders.order_time) as day,
sum(line_items.unit_price * quantity) - sum(coalesce(coupons.amount, 0))
- sum(line_items.unit_price * quantity * coalesce(percentage, 0) / 100) as earned,
max(orders.order_time) as last_time
from orders
inner join line_items on orders.id = line_items.order_id
left outer join coupons on coupons.id = orders.coupon_id
where status = 'C' and payment_type != 'Free'
group by year, month, day
order by last_time desc limit 8
date_part('month', orders.order_time) as month,
date_part('day', orders.order_time) as day,
sum(line_items.unit_price * quantity) - sum(coalesce(coupons.amount, 0))
- sum(line_items.unit_price * quantity * coalesce(percentage, 0) / 100) as earned,
max(orders.order_time) as last_time
from orders
inner join line_items on orders.id = line_items.order_id
left outer join coupons on coupons.id = orders.coupon_id
where status = 'C' and payment_type != 'Free'
group by year, month, day
order by last_time desc limit 8