Thread: Stuck Up In My Own Category Tree

Stuck Up In My Own Category Tree

From
Don Parris
Date:
Hi all,

Note: I'm happy to read howtos, tutorials, archived messages - I just haven't found anything that addresses this yet.  I found a related topic on the novice list, but my post got no response there.  I've been struggling with this for about a week now and need to figure out a solution.  Heck, this may not even be the best approach to hierarchical structures, but it sure seemed reasonable when I first read up on the subject.  Anyway...

I created a category table like so (I got the idea from a website somewhere that used it in a different way, and did not discuss much about addressing what I want to accomplish):
cat_id(serial)  |  cat_name(varchar)  |  parent_id(int)  |  lineage(varchar)  |  deep(int)
1                         root_cat_a                 Null                   1                          1
2                         sub_cat_1                 1                       1-2                       2
3                         sub_sub_cat_a          2                      1-2-3                     3

I use this to categorize transactions, and use the most appropriate subcategory for any given transation item in a table called trans_details.  I can easily show transaction amounts by sub-category  (SELECT cat_name, sum(amount) AS "amount" FROM category, trans_details WHERE category_cat_id = trans_details.cat_id):

cat_name                             |    amount
Transportation: Auto: Fuel     |      $100
Transportation: Auto: Maint    |      $150
Transportation: Fares: Bus    |      $40

but what I cannot figure out is how to create a summary where I show
cat_name                    |    amount
Transportation: Auto     |      $250

or, what I *really* want:
cat_name            |    amount
Transportation     |      $290


Can anyone help me work through this?  Frankly, I'm not even sure where to begin to solve the problem.  I have been trying the WITH RECURSIVE feature, but I do not understand very well how to apply it.  The example query I have brings up an empty result set and I don't have a clue how I could modify it to make it work.  I'm not even sure it's the best method, but it seems like a reasonable approach.  I thought about using regexp to try and match the initial part of the lineage to the category_id of the parents, something like:
WHERE c1.category_id = regexp_matches(c2.lineage, 'nnn-'), but lineage is a text type column, rather than an int, and would need to be cast.

One of the problems I encounter is that a root category with no sub-categories (naturally) won't show up in the category_id = parent_id matches, since such a category has no children.

I found an e-mail from an old thread on this topic on the novice list.  The author created a very similar table to mine, but talks more about how to select the child categories, not the root.  And, frankly, his example sql statements did not bring up the results I would expect.  The issue seems to be somewhat common - I just don't yet have the experience to understand it well yet.  :-)

id      info    parent_id level node_id
1       Name1   Null    1       1
2       Name2   1       2       2
3       Name3   2       3       3
4      Name4    3       4       4
5      Name5    4       5       5
6      Name5    1       2       6
7      Name6    6       3       7
8      Name7    1       2       8


--
D.C. Parris, FMP, LEED AP O+M, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
GPG Key ID: F5E179BE

Re: Stuck Up In My Own Category Tree

From
Samuel Gendler
Date:


On Thu, Aug 11, 2011 at 8:39 AM, Don Parris <parrisdc@gmail.com> wrote:
Hi all,

Note: I'm happy to read howtos, tutorials, archived messages - I just haven't found anything that addresses this yet.  I found a related topic on the novice list, but my post got no response there.  I've been struggling with this for about a week now and need to figure out a solution.  Heck, this may not even be the best approach to hierarchical structures, but it sure seemed reasonable when I first read up on the subject.  Anyway...

I created a category table like so (I got the idea from a website somewhere that used it in a different way, and did not discuss much about addressing what I want to accomplish):
cat_id(serial)  |  cat_name(varchar)  |  parent_id(int)  |  lineage(varchar)  |  deep(int)
1                         root_cat_a                 Null                   1                          1
2                         sub_cat_1                 1                       1-2                       2
3                         sub_sub_cat_a          2                      1-2-3                     3

I use this to categorize transactions, and use the most appropriate subcategory for any given transation item in a table called trans_details.  I can easily show transaction amounts by sub-category  (SELECT cat_name, sum(amount) AS "amount" FROM category, trans_details WHERE category_cat_id = trans_details.cat_id):

cat_name                             |    amount
Transportation: Auto: Fuel     |      $100
Transportation: Auto: Maint    |      $150
Transportation: Fares: Bus    |      $40

but what I cannot figure out is how to create a summary where I show
cat_name                    |    amount
Transportation: Auto     |      $250

or, what I *really* want:
cat_name            |    amount
Transportation     |      $290


Can anyone help me work through this?  Frankly, I'm not even sure where to begin to solve the problem.  I have been trying the WITH RECURSIVE feature, but I do not understand very well how to apply it.  The example query I have brings up an empty result set and I don't have a clue how I could modify it to make it work.  I'm not even sure it's the best method, but it seems like a reasonable approach.  I thought about using regexp to try and match the initial part of the lineage to the category_id of the parents, something like:
WHERE c1.category_id = regexp_matches(c2.lineage, 'nnn-'), but lineage is a text type column, rather than an int, and would need to be cast.

One of the problems I encounter is that a root category with no sub-categories (naturally) won't show up in the category_id = parent_id matches, since such a category has no children.



That link appears to answer all of your potential questions - how to render the hierarchy, how to find all children of a node, etc.  


Re: Stuck Up In My Own Category Tree

From
jasmin.dizdarevic@gmail.com
Date:
I will have do something like this soon. Look at ltree. I don't have much experience with it yet, but it seems that this ext addresses those issues.
Regards

A1 präsentiert BlackBerry® von Vodafone


From: Samuel Gendler <sgendler@ideasculptor.com>
Sender: pgsql-sql-owner@postgresql.org
Date: Thu, 11 Aug 2011 09:24:41 -0700
To: Don Parris<parrisdc@gmail.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: [SQL] Stuck Up In My Own Category Tree



On Thu, Aug 11, 2011 at 8:39 AM, Don Parris <parrisdc@gmail.com> wrote:
Hi all,

Note: I'm happy to read howtos, tutorials, archived messages - I just haven't found anything that addresses this yet.  I found a related topic on the novice list, but my post got no response there.  I've been struggling with this for about a week now and need to figure out a solution.  Heck, this may not even be the best approach to hierarchical structures, but it sure seemed reasonable when I first read up on the subject.  Anyway...

I created a category table like so (I got the idea from a website somewhere that used it in a different way, and did not discuss much about addressing what I want to accomplish):
cat_id(serial)  |  cat_name(varchar)  |  parent_id(int)  |  lineage(varchar)  |  deep(int)
1                         root_cat_a                 Null                   1                          1
2                         sub_cat_1                 1                       1-2                       2
3                         sub_sub_cat_a          2                      1-2-3                     3

I use this to categorize transactions, and use the most appropriate subcategory for any given transation item in a table called trans_details.  I can easily show transaction amounts by sub-category  (SELECT cat_name, sum(amount) AS "amount" FROM category, trans_details WHERE category_cat_id = trans_details.cat_id):

cat_name                             |    amount
Transportation: Auto: Fuel     |      $100
Transportation: Auto: Maint    |      $150
Transportation: Fares: Bus    |      $40

but what I cannot figure out is how to create a summary where I show
cat_name                    |    amount
Transportation: Auto     |      $250

or, what I *really* want:
cat_name            |    amount
Transportation     |      $290


Can anyone help me work through this?  Frankly, I'm not even sure where to begin to solve the problem.  I have been trying the WITH RECURSIVE feature, but I do not understand very well how to apply it.  The example query I have brings up an empty result set and I don't have a clue how I could modify it to make it work.  I'm not even sure it's the best method, but it seems like a reasonable approach.  I thought about using regexp to try and match the initial part of the lineage to the category_id of the parents, something like:
WHERE c1.category_id = regexp_matches(c2.lineage, 'nnn-'), but lineage is a text type column, rather than an int, and would need to be cast.

One of the problems I encounter is that a root category with no sub-categories (naturally) won't show up in the category_id = parent_id matches, since such a category has no children.



That link appears to answer all of your potential questions - how to render the hierarchy, how to find all children of a node, etc.  


Re: Stuck Up In My Own Category Tree

From
"Edward W. Rouse"
Date:

How about SELECT cat_name, sum(amount) AS "amount" FROM category, trans_details WHERE category_cat_id in (select cat_id from category where lineage ~ ‘^1’)

 

Where the in clause is basically, the cat_id where lineage starts with, and then the lineage you want. 1, 1-2, whatever the lineage is. Not sure about efficiency or whether you can put another subselect in there to build the lineage string to match, but the basic idea should work.

 

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Don Parris
Sent: Thursday, August 11, 2011 11:39 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Stuck Up In My Own Category Tree

 

Hi all,

Note: I'm happy to read howtos, tutorials, archived messages - I just haven't found anything that addresses this yet.  I found a related topic on the novice list, but my post got no response there.  I've been struggling with this for about a week now and need to figure out a solution.  Heck, this may not even be the best approach to hierarchical structures, but it sure seemed reasonable when I first read up on the subject.  Anyway...

I created a category table like so (I got the idea from a website somewhere that used it in a different way, and did not discuss much about addressing what I want to accomplish):
cat_id(serial)  |  cat_name(varchar)  |  parent_id(int)  |  lineage(varchar)  |  deep(int)
1                         root_cat_a                 Null                   1                          1
2                         sub_cat_1                 1                       1-2                       2
3                         sub_sub_cat_a          2                      1-2-3                     3

I use this to categorize transactions, and use the most appropriate subcategory for any given transation item in a table called trans_details.  I can easily show transaction amounts by sub-category  (SELECT cat_name, sum(amount) AS "amount" FROM category, trans_details WHERE category_cat_id = trans_details.cat_id):

cat_name                             |    amount

Transportation: Auto: Fuel     |      $100
Transportation: Auto: Maint    |      $150
Transportation: Fares: Bus    |      $40

but what I cannot figure out is how to create a summary where I show
cat_name                    |    amount
Transportation: Auto     |      $250

or, what I *really* want:
cat_name            |    amount
Transportation     |      $290


Can anyone help me work through this?  Frankly, I'm not even sure where to begin to solve the problem.  I have been trying the WITH RECURSIVE feature, but I do not understand very well how to apply it.  The example query I have brings up an empty result set and I don't have a clue how I could modify it to make it work.  I'm not even sure it's the best method, but it seems like a reasonable approach.  I thought about using regexp to try and match the initial part of the lineage to the category_id of the parents, something like:
WHERE c1.category_id = regexp_matches(c2.lineage, 'nnn-'), but lineage is a text type column, rather than an int, and would need to be cast.

One of the problems I encounter is that a root category with no sub-categories (naturally) won't show up in the category_id = parent_id matches, since such a category has no children.

I found an e-mail from an old thread on this topic on the novice list.  The author created a very similar table to mine, but talks more about how to select the child categories, not the root.  And, frankly, his example sql statements did not bring up the results I would expect.  The issue seems to be somewhat common - I just don't yet have the experience to understand it well yet.  :-)

id      info    parent_id level node_id
1       Name1   Null    1       1
2       Name2   1       2       2
3       Name3   2       3       3
4      Name4    3       4       4
5      Name5    4       5       5
6      Name5    1       2       6
7      Name6    6       3       7
8      Name7    1       2       8



--
D.C. Parris, FMP, LEED AP O+M, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate

GPG Key ID: F5E179BE

 

Re: Stuck Up In My Own Category Tree

From
Don Parris
Date:
On Thu, Aug 11, 2011 at 12:48, Edward W. Rouse <erouse@comsquared.com> wrote:

How about SELECT cat_name, sum(amount) AS "amount" FROM category, trans_details WHERE category_cat_id in (select cat_id from category where lineage ~ ‘^1’)

 

Where the in clause is basically, the cat_id where lineage starts with, and then the lineage you want. 1, 1-2, whatever the lineage is. Not sure about efficiency or whether you can put another subselect in there to build the lineage string to match, but the basic idea should work.

 



Thanks all, I will try out the suggestions.  Samuel, thanks for the link!  Haven't had a chance to check it out yet, but it sure looks promising.  I'll post again when I've had a chance to look over/ try out some of these things.  :-)

Don
--
D.C. Parris, FMP, LEED AP O+M, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
GPG Key ID: F5E179BE