I've such beast:
select
sm.ShipMethodID as _ShipMethodID,
sm.Name as _Name, sm.Description as _Description from
(
select sum(bi.qty) as N, sum(i.peso) as W,
sum(i.price*bi.qty) as _price
from shop_commerce_baskets b
join shop_commerce_basket_items bi on bi.basketid=b.basketid
join catalog_items i on bi.itemid=i.itemid
where b.basketid=20
) as _subselect,
shop_commerce_shipmethods sm
where
sm.MinN<=_subselect.N and sm.MaxN>=_subselect.N
and
sm.MinW<=_subselect.W and sm.MaxW>=_subselect.W;
And that works pretty fine and fast enough for the context.
shop_commerce_shipmethods is a set of constraint and characteristics
of a shipping method.
Now I'd like to split the basket into on stock and backorder and
return everything in one query and see what shipping methods are
available for the on stock and backorder part.
catalog_items contains availability.
One way to obtain what I'm looking for would be:
- write a function onstock(int,int) that return availability if
qty>availability and qty if qty<=availability
- write a function backorder(int, int) that return (qty-availability)
if qty>availability otherwise return 0
- make a UNION of 2 of the above select one using finction
onstock(...), the other using function backorder() in spite of qty + 1
flag to signal if I'm returning an "on stock" or "backorder"
permitted shipmethod.
Another way would be to create a temp of the
shop_commerce_basket_item with onstock/backorder qty.
One more way could be use a for and compute "manually" the aggregate
function doing something similar to what I could do on the client
side.
But I see a lot of code duplication and a query that is growing out
of control and I've the feeling this thing will bite me as soon as
I'll have to add constraint to the shipping logic or I'll have to
refactor where the data related to the shipping logic are stored.
My main concern now is readability and ease of refactoring.
Could anyone point me to some general SQL technique or some pgsql
secret weapon that could make the above plan cleaner?
I'm on 8.1.
thx
--
Ivan Sergio Borgonovo
http://www.webthatworks.it