agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedReturn product category with hierarchical info
5+ messages / 3 participants
[nested] [flat]
* Return product category with hierarchical info
@ 2022-01-05 12:19 Richard Klingler <richard@klingler.net>
0 siblings, 1 reply; 5+ messages in thread
From: Richard Klingler @ 2022-01-05 12:19 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
Good afternoon (o;
First of all, am I am totally no expert in using PGSQL but use it mainly
for simple web applications...
Now I have a table which represents the categories for products in a
hierarchical manner:
id | name | parent
So a top category is represented with parent being 0:
1 | 'Living' | 0
The next level would look:
2 | 'Decoration' | 1
And the last level (only 3 levels):
3 | 'Table' | 2
So far I'm using this query to get all 3rd level categories as I use the
output for datatables editor as a product can only belong to the lowest
category:
select id, name from category
where parent in (select id from category where parent in (select id from
category where parent = 0))
But this has a problem as more than one 3rd level category can have the
same name, therefore difficult to distinguish in the datatables editor
which one is right.
So now my question (finally ;o):
Is there a simple query that would return all 3rd levels category ids
and names together with the concatenated names of the upper levels?
Something like:
3 | 'Table' | 'Living - Decoration'
thanks in advance
richard
PS: If someone could recommend a good ebook or online resource for such
stupid questions, even better (o;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Return product category with hierarchical info
@ 2022-01-05 12:28 Oliveiros Cristina <oliveiros.cristina@gmail.com>
parent: Richard Klingler <richard@klingler.net>
0 siblings, 1 reply; 5+ messages in thread
From: Oliveiros Cristina @ 2022-01-05 12:28 UTC (permalink / raw)
To: Richard Klingler <richard@klingler.net>; +Cc: pgsql-sql@lists.postgresql.org
I’m also no expert, it’s been a decade or so since I do not use psql
But maybe
Select level3.id, level3.name, level2.name, level1.name,
From category level3
Join category level2
On level2.Id = level3.parent
Join category level1
On level1.Id = level2.parent
Best,
Oliver
Sent from Oliver’s iPhone
> On 5 Jan 2022, at 12:19, Richard Klingler <richard@klingler.net> wrote:
>
> Good afternoon (o;
>
>
> First of all, am I am totally no expert in using PGSQL but use it mainly for simple web applications...
>
>
> Now I have a table which represents the categories for products in a hierarchical manner:
>
> id | name | parent
>
>
> So a top category is represented with parent being 0:
>
> 1 | 'Living' | 0
>
>
> The next level would look:
>
> 2 | 'Decoration' | 1
>
>
> And the last level (only 3 levels):
>
> 3 | 'Table' | 2
>
>
> So far I'm using this query to get all 3rd level categories as I use the output for datatables editor as a product can only belong to the lowest category:
>
> select id, name from category
> where parent in (select id from category where parent in (select id from category where parent = 0))
>
> But this has a problem as more than one 3rd level category can have the same name, therefore difficult to distinguish in the datatables editor which one is right.
>
>
> So now my question (finally ;o):
>
>
> Is there a simple query that would return all 3rd levels category ids and names together with the concatenated names of the upper levels? Something like:
>
>
> 3 | 'Table' | 'Living - Decoration'
>
>
> thanks in advance
>
> richard
>
>
>
> PS: If someone could recommend a good ebook or online resource for such stupid questions, even better (o;
>
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Return product category with hierarchical info
@ 2022-01-05 12:37 Richard Klingler <richard@klingler.net>
parent: Oliveiros Cristina <oliveiros.cristina@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Richard Klingler @ 2022-01-05 12:37 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
Hello Oliver
Exactly that's it...I knew some "join" would be involved...but couldn't
find the right example ;-)
thanks for the quick help :-)
richard
On 1/5/22 13:28, Oliveiros Cristina wrote:
> I’m also no expert, it’s been a decade or so since I do not use psql
> But maybe
>
> Select level3.id, level3.name, level2.name, level1.name,
> From category level3
> Join category level2
> On level2.Id = level3.parent
> Join category level1
> On level1.Id = level2.parent
>
> Best,
> Oliver
>
> Sent from Oliver’s iPhone
>
>> On 5 Jan 2022, at 12:19, Richard Klingler <richard@klingler.net> wrote:
>>
>> Good afternoon (o;
>>
>>
>> First of all, am I am totally no expert in using PGSQL but use it
>> mainly for simple web applications...
>>
>>
>> Now I have a table which represents the categories for products in a
>> hierarchical manner:
>>
>> id | name | parent
>>
>>
>> So a top category is represented with parent being 0:
>>
>> 1 | 'Living' | 0
>>
>>
>> The next level would look:
>>
>> 2 | 'Decoration' | 1
>>
>>
>> And the last level (only 3 levels):
>>
>> 3 | 'Table' | 2
>>
>>
>> So far I'm using this query to get all 3rd level categories as I use
>> the output for datatables editor as a product can only belong to the
>> lowest category:
>>
>> select id, name from category
>> where parent in (select id from category where parent in (select id
>> from category where parent = 0))
>>
>> But this has a problem as more than one 3rd level category can have
>> the same name, therefore difficult to distinguish in the datatables
>> editor which one is right.
>>
>>
>> So now my question (finally ;o):
>>
>>
>> Is there a simple query that would return all 3rd levels category ids
>> and names together with the concatenated names of the upper levels?
>> Something like:
>>
>>
>> 3 | 'Table' | 'Living - Decoration'
>>
>>
>> thanks in advance
>>
>> richard
>>
>>
>>
>> PS: If someone could recommend a good ebook or online resource for
>> such stupid questions, even better (o;
>>
>>
>>
>>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Return product category with hierarchical info
@ 2022-01-05 13:00 Oliveiros Cristina <oliveiros.cristina@gmail.com>
parent: Richard Klingler <richard@klingler.net>
1 sibling, 0 replies; 5+ messages in thread
From: Oliveiros Cristina @ 2022-01-05 13:00 UTC (permalink / raw)
To: Richard Klingler <richard@klingler.net>; +Cc: pgsql-sql@lists.postgresql.org
No worries
Glad it helped !
Best,
Oliver
Sent from Oliver’s iPhone
> On 5 Jan 2022, at 12:38, Richard Klingler <richard@klingler.net> wrote:
>
>
> Hello Oliver
>
>
>
> Exactly that's it...I knew some "join" would be involved...but couldn't find the right example ;-)
>
>
>
> thanks for the quick help :-)
>
> richard
>
>
>
> On 1/5/22 13:28, Oliveiros Cristina wrote:
>> I’m also no expert, it’s been a decade or so since I do not use psql
>> But maybe
>>
>> Select level3.id, level3.name, level2.name, level1.name,
>> From category level3
>> Join category level2
>> On level2.Id = level3.parent
>> Join category level1
>> On level1.Id = level2.parent
>>
>> Best,
>> Oliver
>>
>> Sent from Oliver’s iPhone
>>
>>> On 5 Jan 2022, at 12:19, Richard Klingler <richard@klingler.net> wrote:
>>>
>>> Good afternoon (o;
>>>
>>>
>>> First of all, am I am totally no expert in using PGSQL but use it mainly for simple web applications...
>>>
>>>
>>> Now I have a table which represents the categories for products in a hierarchical manner:
>>>
>>> id | name | parent
>>>
>>>
>>> So a top category is represented with parent being 0:
>>>
>>> 1 | 'Living' | 0
>>>
>>>
>>> The next level would look:
>>>
>>> 2 | 'Decoration' | 1
>>>
>>>
>>> And the last level (only 3 levels):
>>>
>>> 3 | 'Table' | 2
>>>
>>>
>>> So far I'm using this query to get all 3rd level categories as I use the output for datatables editor as a product can only belong to the lowest category:
>>>
>>> select id, name from category
>>> where parent in (select id from category where parent in (select id from category where parent = 0))
>>>
>>> But this has a problem as more than one 3rd level category can have the same name, therefore difficult to distinguish in the datatables editor which one is right.
>>>
>>>
>>> So now my question (finally ;o):
>>>
>>>
>>> Is there a simple query that would return all 3rd levels category ids and names together with the concatenated names of the upper levels? Something like:
>>>
>>>
>>> 3 | 'Table' | 'Living - Decoration'
>>>
>>>
>>> thanks in advance
>>>
>>> richard
>>>
>>>
>>>
>>> PS: If someone could recommend a good ebook or online resource for such stupid questions, even better (o;
>>>
>>>
>>>
>>>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Return product category with hierarchical info
@ 2022-01-05 16:42 Steve Midgley <science@misuse.org>
parent: Richard Klingler <richard@klingler.net>
1 sibling, 0 replies; 5+ messages in thread
From: Steve Midgley @ 2022-01-05 16:42 UTC (permalink / raw)
To: Richard Klingler <richard@klingler.net>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
On Wed, Jan 5, 2022 at 4:38 AM Richard Klingler <richard@klingler.net>
wrote:
> Hello Oliver
>
>
> Exactly that's it...I knew some "join" would be involved...but couldn't
> find the right example ;-)
>
>
> thanks for the quick help :-)
>
> richard
>
>
> On 1/5/22 13:28, Oliveiros Cristina wrote:
>
> I’m also no expert, it’s been a decade or so since I do not use psql
> But maybe
>
> Select level3.id, level3.name, level2.name, level1.name,
> From category level3
> Join category level2
> On level2.Id = level3.parent
> Join category level1
> On level1.Id = level2.parent
>
> Best,
> Oliver
>
> Sent from Oliver’s iPhone
>
> On 5 Jan 2022, at 12:19, Richard Klingler <richard@klingler.net>
> <richard@klingler.net> wrote:
>
> Good afternoon (o;
>
>
> First of all, am I am totally no expert in using PGSQL but use it mainly
> for simple web applications...
>
>
> Now I have a table which represents the categories for products in a
> hierarchical manner:
>
> id | name | parent
>
>
> So a top category is represented with parent being 0:
>
> 1 | 'Living' | 0
>
>
> The next level would look:
>
> 2 | 'Decoration' | 1
>
>
> And the last level (only 3 levels):
>
> 3 | 'Table' | 2
>
>
> So far I'm using this query to get all 3rd level categories as I use the
> output for datatables editor as a product can only belong to the lowest
> category:
>
> select id, name from category
> where parent in (select id from category where parent in (select id from
> category where parent = 0))
>
> But this has a problem as more than one 3rd level category can have the
> same name, therefore difficult to distinguish in the datatables editor
> which one is right.
>
>
> So now my question (finally ;o):
>
>
> Is there a simple query that would return all 3rd levels category ids and
> names together with the concatenated names of the upper levels? Something
> like:
>
>
> 3 | 'Table' | 'Living - Decoration'
>
>
> thanks in advance
>
> richard
>
>
>
> PS: If someone could recommend a good ebook or online resource for such
> stupid questions, even better (o;
>
>
>
That query will work fine. But if you have variable tree depth (sometimes
1, 2, 3, or n), you might consider using a recursive query, which doesn't
care how deep you have to query:
https://www.postgresql.org/docs/current/queries-with.html
I haven't tried to create a demo using your data, but let us know if you
can't figure it out (assuming you even need recursion for your use case).
Steve
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2022-01-05 16:42 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-01-05 12:19 Return product category with hierarchical info Richard Klingler <richard@klingler.net>
2022-01-05 12:28 ` Oliveiros Cristina <oliveiros.cristina@gmail.com>
2022-01-05 12:37 ` Richard Klingler <richard@klingler.net>
2022-01-05 13:00 ` Oliveiros Cristina <oliveiros.cristina@gmail.com>
2022-01-05 16:42 ` Steve Midgley <science@misuse.org>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox