Thread

1. Return product category with hierarchical info

   Richard Klingler <richard@klingler.net> — 2022-01-05T12:19:11Z

   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;
   
   
   
   
   
   

2. Re: Return product category with hierarchical info

   Oliveiros Cristina <oliveiros.cristina@gmail.com> — 2022-01-05T12:28:23Z

   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;
   > 
   > 
   > 
   > 
   

3. Re: Return product category with hierarchical info

   Richard Klingler <richard@klingler.net> — 2022-01-05T12:37:55Z

   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;
   >>
   >>
   >>
   >>

4. Re: Return product category with hierarchical info

   Oliveiros Cristina <oliveiros.cristina@gmail.com> — 2022-01-05T13:00:09Z

   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;
   >>> 
   >>> 
   >>> 
   >>> 
   

5. Re: Return product category with hierarchical info

   Steve Midgley <science@misuse.org> — 2022-01-05T16:42:55Z

   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