Обсуждение: Clean up shop database
Good morning (o; I am in the process of migrating an online shop to another system and therefore also want to clean out products that haven't been re-stocked for a time. Now this simple query returns all order ids younger than 750 days: select orderid, orderdate from orders where (now() - orderdate) < INTERVAL '1000 days' order by orderdate asc So it shows me orders beginning from January 1st 2020...all fine. Now I want to list all products which stock is 0 and have only been ordered before those 750 days..so I use the above query in wrap it in the select with a "not in": select p.productid as id, p.name_de as name from product p, orderitems i, orders where p.productid = i.orderitems2productid and i.orderitems2orderid not in (select orderid from orders where (now() - orderdate) < INTERVAL '750 days') and p.pieces < 1 and p.active = 't' group by id order by id desc Besides that this query takes over 70 seconds...it also returns products that have been ordered after January 1st 2020. So somehow this "not in" doesn't work as I am expecting it (o; thanks in advance richard
On 1/19/22 04:03, Richard Klingler wrote: > Good morning (o; > > > I am in the process of migrating an online shop to another system and > therefore > also want to clean out products that haven't been re-stocked for a time. > > Now this simple query returns all order ids younger than 750 days: > > select orderid, orderdate from orders > where (now() - orderdate) < INTERVAL '1000 days' > order by orderdate asc > > So it shows me orders beginning from January 1st 2020...all fine. > > > Now I want to list all products which stock is 0 and have only been > ordered > before those 750 days..so I use the above query in wrap it in the select > with a "not in": > > select p.productid as id, p.name_de as name > from product p, orderitems i, orders > where p.productid = i.orderitems2productid > and i.orderitems2orderid not in (select orderid from orders where > (now() - orderdate) < INTERVAL '750 days') > and p.pieces < 1 > and p.active = 't' > group by id > order by id desc something like this? select p.productid as id, p.name_de as name from product p join orderitems i on p.productid = i.orderitems2productid join orders o on i.orderid = o.orderid where o.orderdate < 'January 1st 2020' and p.pieces < 1 and p.active = 't' group by id order by id desc > > > Besides that this query takes over 70 seconds...it also returns > products that have been ordered after January 1st 2020. > > So somehow this "not in" doesn't work as I am expecting it (o; > > > thanks in advance > richard > > >
Odd...gives me the same result.... Tried another approach as the ordered is known where to start from....but still the same: select p.productid as id, p.name_de as name from product p, orderitems i,orders where p.productid = i.orderitems2productid and i.orderitems2orderid = orders.orderid and orders.orderid < 14483 and p.pieces < 1 and p.active = 't' group by id order by id desc Still lists products after January 1st 2021...but I know what is going on.... For example the query above returns as the first product id 47387: id name 47387 Carpet 70x120cm 47373 Mug Mynte Lavender Now when I look for order items where this product is: select o.orderid, o.orderdate, i.orderitemsid, p.productid from orders o, orderitems i, product p where p.productid = 47387 and p.productid = i.orderitems2productid and o.orderid = i.orderitems2orderid It gives me: orderid orderdate orderitemsid productid 19157 2021-02-08 88304 47387 17600 2020-10-13 81281 47387 14462 2019-12-28 67561 47387 So the initial query somehow gives all products that have been ordered at least before January 1st 2021 but not only before that date. cheers richard On Wed, 19 Jan 2022 05:04:09 -0700, Rob Sargent wrote: > On 1/19/22 04:03, Richard Klingler wrote: >> Good morning (o; >> >> >> I am in the process of migrating an online shop to another system and >> therefore >> also want to clean out products that haven't been re-stocked for a time. >> >> Now this simple query returns all order ids younger than 750 days: >> >> select orderid, orderdate from orders >> where (now() - orderdate) < INTERVAL '1000 days' >> order by orderdate asc >> >> So it shows me orders beginning from January 1st 2020...all fine. >> >> >> Now I want to list all products which stock is 0 and have only been >> ordered >> before those 750 days..so I use the above query in wrap it in the select >> with a "not in": >> >> select p.productid as id, p.name_de as name >> from product p, orderitems i, orders >> where p.productid = i.orderitems2productid >> and i.orderitems2orderid not in (select orderid from orders where >> (now() - orderdate) < INTERVAL '750 days') >> and p.pieces < 1 >> and p.active = 't' >> group by id >> order by id desc > something like this? > > select p.productid as id, p.name_de as name > from product p join orderitems i on p.productid = i.orderitems2productid > join orders o on i.orderid = o.orderid > where o.orderdate < 'January 1st 2020' > and p.pieces < 1 > and p.active = 't' > group by id > order by id desc >> >> >> Besides that this query takes over 70 seconds...it also returns >> products that have been ordered after January 1st 2020. >> >> So somehow this "not in" doesn't work as I am expecting it (o; >> >> >> thanks in advance >> richard >> >> >> > > >
On 1/19/22 05:40, Richard Klingler wrote: > Odd...gives me the same result.... > > Tried another approach as the ordered is known where to start > from....but still the same: > > select p.productid as id, p.name_de as name > from product p, orderitems i,orders > where p.productid = i.orderitems2productid > and i.orderitems2orderid = orders.orderid > and orders.orderid < 14483 > and p.pieces < 1 > and p.active = 't' > group by id > order by id desc > > Still lists products after January 1st 2021...but I know what is going > on.... (On this list top-posting is frowned upon. Inline or bottom-posting preferred.) The above query does not restrict order date? select p.*,max(o.orderdate) from product p join orderitem t on p.productid = t.orderitems2productid join order o on t.orderitems2orderid = o.orderid group by p.productid having max(o.orderdate < 'January 1 2021' >
On Wed, 19 Jan 2022 05:56:17 -0700, Rob Sargent wrote: > On 1/19/22 05:40, Richard Klingler wrote: >> Odd...gives me the same result.... >> >> Tried another approach as the ordered is known where to start >> from....but still the same: >> >> select p.productid as id, p.name_de as name >> from product p, orderitems i,orders >> where p.productid = i.orderitems2productid >> and i.orderitems2orderid = orders.orderid >> and orders.orderid < 14483 >> and p.pieces < 1 >> and p.active = 't' >> group by id >> order by id desc >> >> Still lists products after January 1st 2021...but I know what is going >> on.... > > (On this list top-posting is frowned upon. Inline or bottom-posting > preferred.) > The above query does not restrict order date? > > select p.*,max(o.orderdate) > from product p join orderitem t on p.productid = t.orderitems2productid > join order o on t.orderitems2orderid = o.orderid > group by p.productid > having max(o.orderdate < 'January 1 2021' > Ah sorry for that..... No it does not...but I assume it lists products that where at least ordered before January 1st 2021 as it contains lower product IDs. Hmm..gives me: > ERROR: function max(boolean) does not exist LINE 5: having max(o.orderdate < '2021-01-01')
> No it does not...but I assume it lists products that where at least > ordered before January 1st 2021 > as it contains lower product IDs. > > Hmm..gives me: > >> ERROR: function max(boolean) does not exist > LINE 5: having max(o.orderdate < '2021-01-01') > > Sorry > > max(o.orderdate )< '2021-01-01' >
On Wed, 19 Jan 2022 06:32:19 -0700, Rob Sargent wrote: > >> No it does not...but I assume it lists products that where at least >> ordered before January 1st 2021 >> as it contains lower product IDs. >> >> Hmm..gives me: >> >>> ERROR: function max(boolean) does not exist >> LINE 5: having max(o.orderdate < '2021-01-01') >> >> > Sorry >> >> max(o.orderdate )< '2021-01-01' > >> You're a genius :-) Looks perfect... kiitoksia richard
max(o.orderdate )< '2021-01-01' >> >>> > > You're a genius :-) > > Looks perfect... > No, no. The smart guys haven’t showed up yet. Cheers > > kiitoksia > richard > >
> From: Richard Klingler <richard@klingler.net> Sent: Wednesday, January 19, 2022 5:37 AM > On Wed, 19 Jan 2022 06:32:19 -0700, Rob Sargent wrote: > > > >> No it does not...but I assume it lists products that where at least > >> ordered before January 1st 2021 as it contains lower product IDs. > >> > >> Hmm..gives me: > >> > >>> ERROR: function max(boolean) does not exist > >> LINE 5: having max(o.orderdate < '2021-01-01') > >> > >> > > Sorry > >> > >> max(o.orderdate )< '2021-01-01' > > > >> > You're a genius :-) > Looks perfect... There is one caveat here: unless you are 100% certain that the product.pieces count is 100% accurate, you should sum the orderitems.productid. It is pretty common for the indirect counts (like product.pieces) to get out of sync with the "normal" source of truth (line items). This does depend on design of your system, of course. Mike
On Wed, 19 Jan 2022 06:41:49 -0700, Rob Sargent wrote: > > max(o.orderdate )< '2021-01-01' >>> >>>> >> >> You're a genius :-) >> >> Looks perfect... >> > > No, no. The smart guys haven’t showed up yet. > Cheers > Hmm..almost forgot..... Now how would I turn this into an update statement based on the previous query?
On Wed, 19 Jan 2022 05:51:56 -0800, Mike Sofen wrote: >> From: Richard Klingler <richard@klingler.net> Sent: Wednesday, January > 19, 2022 5:37 AM >> On Wed, 19 Jan 2022 06:32:19 -0700, Rob Sargent wrote: >>> >>>> No it does not...but I assume it lists products that where at least >>>> ordered before January 1st 2021 as it contains lower product IDs. >>>> >>>> Hmm..gives me: >>>> >>>>> ERROR: function max(boolean) does not exist >>>> LINE 5: having max(o.orderdate < '2021-01-01') >>>> >>>> >>> Sorry >>>> >>>> max(o.orderdate )< '2021-01-01' >>> >>>> > >> You're a genius :-) > >> Looks perfect... > > There is one caveat here: unless you are 100% certain that the > product.pieces count is 100% accurate, you should sum the > orderitems.productid. It is pretty common for the indirect counts (like > product.pieces) to get out of sync with the "normal" source of truth (line > items). > > This does depend on design of your system, of course. > > Mike > Well I use the productid for rough checking so that they are created some time before the specified date. And I won't delete any records..just mark them as inactive... that way datatables loads much faster when it just grabs the active products via ajax call.
> Hmm..almost forgot..... > > Now how would I turn this into an update statement based on the > previous query? > update what? A CTE comes to mind but maybe update product p set some=thing where p.productid = m.productid from (select p.productid,max(o.orderdate) from product p join orderitem t on p.productid = t.orderitems2productid join order o on t.orderitems2orderid = o.orderid group by p.productid having max(o.orderdate < 'January 1 2021') as m