Stuck Up In My Own Category Tree
| От | Don Parris |
|---|---|
| Тема | Stuck Up In My Own Category Tree |
| Дата | |
| Msg-id | CAJ-7yonw4_qDCp-ZNYwEkR2jdLKeL8nfGc+-TLLSW=Rmo1VkbA@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Stuck Up In My Own Category Tree
(Samuel Gendler <sgendler@ideasculptor.com>)
Re: Stuck Up In My Own Category Tree ("Edward W. Rouse" <erouse@comsquared.com>) |
| Список | pgsql-sql |
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
--
D.C. Parris, FMP, LEED AP O+M, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
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
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
В списке pgsql-sql по дате отправления: