Re: 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-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org]
On Behalf Of Don Parris
Sent: Thursday, August 11, 2011 11:39 AM
To: pgsql-sql(at)postgresql(dot)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

https://www.xing.com/profile/Don_Parris |
http://www.linkedin.com/in/dcparris

GPG Key ID: F5E179BE