agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedClean up shop database
12+ messages / 3 participants
[nested] [flat]
* Clean up shop database
@ 2022-01-19 11:03 Richard Klingler <richard@klingler.net>
0 siblings, 1 reply; 12+ messages in thread
From: Richard Klingler @ 2022-01-19 11:03 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Clean up shop database
@ 2022-01-19 12:04 Rob Sargent <robjsargent@gmail.com>
parent: Richard Klingler <richard@klingler.net>
0 siblings, 1 reply; 12+ messages in thread
From: Rob Sargent @ 2022-01-19 12:04 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Clean up shop database
@ 2022-01-19 12:40 Richard Klingler <richard@klingler.net>
parent: Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Richard Klingler @ 2022-01-19 12:40 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
>>
>>
>>
>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Clean up shop database
@ 2022-01-19 12:56 Rob Sargent <robjsargent@gmail.com>
parent: Richard Klingler <richard@klingler.net>
0 siblings, 1 reply; 12+ messages in thread
From: Rob Sargent @ 2022-01-19 12:56 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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'
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Clean up shop database
@ 2022-01-19 13:17 Richard Klingler <richard@klingler.net>
parent: Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Richard Klingler @ 2022-01-19 13:17 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
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')
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Clean up shop database
@ 2022-01-19 13:32 Rob Sargent <robjsargent@gmail.com>
parent: Richard Klingler <richard@klingler.net>
0 siblings, 1 reply; 12+ messages in thread
From: Rob Sargent @ 2022-01-19 13:32 UTC (permalink / raw)
To: Richard Klingler <richard@klingler.net>; +Cc: pgsql-sql@lists.postgresql.org
> 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'
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Clean up shop database
@ 2022-01-19 13:37 Richard Klingler <richard@klingler.net>
parent: Rob Sargent <robjsargent@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: Richard Klingler @ 2022-01-19 13:37 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Clean up shop database
@ 2022-01-19 13:41 Rob Sargent <robjsargent@gmail.com>
parent: Richard Klingler <richard@klingler.net>
1 sibling, 1 reply; 12+ messages in thread
From: Rob Sargent @ 2022-01-19 13:41 UTC (permalink / raw)
To: Richard Klingler <richard@klingler.net>; +Cc: pgsql-sql@lists.postgresql.org
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
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* RE: Clean up shop database
@ 2022-01-19 13:51 Mike Sofen <msofen@runbox.com>
parent: Richard Klingler <richard@klingler.net>
1 sibling, 1 reply; 12+ messages in thread
From: Mike Sofen @ 2022-01-19 13:51 UTC (permalink / raw)
To: 'Richard Klingler' <richard@klingler.net>; pgsql-sql@lists.postgresql.org
> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Clean up shop database
@ 2022-01-19 13:57 Richard Klingler <richard@klingler.net>
parent: Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Richard Klingler @ 2022-01-19 13:57 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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?
^ permalink raw reply [nested|flat] 12+ messages in thread
* RE: Clean up shop database
@ 2022-01-19 13:59 Richard Klingler <richard@klingler.net>
parent: Mike Sofen <msofen@runbox.com>
0 siblings, 0 replies; 12+ messages in thread
From: Richard Klingler @ 2022-01-19 13:59 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Clean up shop database
@ 2022-01-19 16:41 Rob Sargent <robjsargent@gmail.com>
parent: Richard Klingler <richard@klingler.net>
0 siblings, 0 replies; 12+ messages in thread
From: Rob Sargent @ 2022-01-19 16:41 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2022-01-19 16:41 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-01-19 11:03 Clean up shop database Richard Klingler <richard@klingler.net>
2022-01-19 12:04 ` Rob Sargent <robjsargent@gmail.com>
2022-01-19 12:40 ` Richard Klingler <richard@klingler.net>
2022-01-19 12:56 ` Rob Sargent <robjsargent@gmail.com>
2022-01-19 13:17 ` Richard Klingler <richard@klingler.net>
2022-01-19 13:32 ` Rob Sargent <robjsargent@gmail.com>
2022-01-19 13:37 ` Richard Klingler <richard@klingler.net>
2022-01-19 13:41 ` Rob Sargent <robjsargent@gmail.com>
2022-01-19 13:57 ` Richard Klingler <richard@klingler.net>
2022-01-19 16:41 ` Rob Sargent <robjsargent@gmail.com>
2022-01-19 13:51 ` Mike Sofen <msofen@runbox.com>
2022-01-19 13:59 ` Richard Klingler <richard@klingler.net>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox