Re: Stuck Up In My Own Category Tree

On Thu, Aug 11, 2011 at 8:39 AM, Don Parris <parrisdc(at)gmail(dot)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.
>

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.