[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 infoparent_id level node_id 1 Name1 Null1 1 2 Name2 1 2 2 3 Name3 2 3 3 4 Name43 4 4 5 Name54 5 5 6 Name51 2 6 7 Name66 3 7 8 Name71 2 8 -- D.C. Parris, FMP, LEED AP O+M, ESL Certificate Minister, Security/FM Coordinator, Free Software Advocate https://www.xing.com/profile/Don_Parris | http://www.linkedin.com/in/dcparris GPG Key ID: F5E179BE
Re: [SQL] Stuck Up In My Own Category Tree
On Thu, Aug 11, 2011 at 8:39 AM, Don Parris 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. > http://old.storytotell.org/blog/2009/08/11/postgresql84-recursive-queries.html 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: [SQL] Stuck Up In My Own Category Tree
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 -Original Message- From: Samuel Gendler Sender: pgsql-sql-owner@postgresql.orgDate: Thu, 11 Aug 2011 09:24:41 To: Don Parris Cc: Subject: Re: [SQL] Stuck Up In My Own Category Tree On Thu, Aug 11, 2011 at 8:39 AM, Don Parris 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. > http://old.storytotell.org/blog/2009/08/11/postgresql84-recursive-queries.html 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: [SQL] Stuck Up In My Own Category Tree
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-ow...@postgresql.org [mailto:pgsql-sql-ow...@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 infoparent_id level node_id 1 Name1 Null1 1 2 Name2 1 2 2 3 Name3 2 3 3 4 Name43 4 4 5 Name54 5 5 6 Name51 2 6 7 Name66 3 7 8 Name71 2 8 -- D.C. Parris, FMP, LEED AP O+M, ESL Certificate Minister, Security/FM Coordinator, Free Software Advocate https://www.xing.com/profile/Don_Parris | http://www.linkedin.com/in/dcparris GPG Key ID: F5E179BE
Re: [SQL] Stuck Up In My Own Category Tree
On Thu, Aug 11, 2011 at 12:48, Edward W. Rouse 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 https://www.xing.com/profile/Don_Parris | http://www.linkedin.com/in/dcparris GPG Key ID: F5E179BE
