Re: Stuck Up In My Own Category Tree - Mailing list pgsql-sql
| From | Edward W. Rouse |
|---|---|
| Subject | Re: Stuck Up In My Own Category Tree |
| Date | |
| Msg-id | 08d801cc5846$83168d30$8943a790$@com Whole thread Raw |
| In response to | Stuck Up In My Own Category Tree (Don Parris <parrisdc@gmail.com>) |
| Responses |
Re: Stuck Up In My Own Category Tree
(Don Parris <parrisdc@gmail.com>)
|
| List | pgsql-sql |
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