Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 4DAF2B5DBF2 for ; Thu, 11 Aug 2011 13:24:50 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 64131-02 for ; Thu, 11 Aug 2011 16:24:43 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-pz0-f42.google.com (mail-pz0-f42.google.com [209.85.210.42]) by mail.postgresql.org (Postfix) with ESMTP id 6D34BB5DBD6 for ; Thu, 11 Aug 2011 13:24:42 -0300 (ADT) Received: by pzk37 with SMTP id 37so6718415pzk.15 for ; Thu, 11 Aug 2011 09:24:42 -0700 (PDT) MIME-Version: 1.0 Received: by 10.143.97.24 with SMTP id z24mr5445167wfl.212.1313079881878; Thu, 11 Aug 2011 09:24:41 -0700 (PDT) Received: by 10.68.51.197 with HTTP; Thu, 11 Aug 2011 09:24:41 -0700 (PDT) X-Originating-IP: [66.215.95.51] In-Reply-To: References: Date: Thu, 11 Aug 2011 09:24:41 -0700 Message-ID: Subject: Re: Stuck Up In My Own Category Tree From: Samuel Gendler To: Don Parris Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=000e0cd2481cebcb7404aa3d3b94 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201108/31 X-Sequence-Number: 35699 --000e0cd2481cebcb7404aa3d3b94 Content-Type: text/plain; charset=ISO-8859-1 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. --000e0cd2481cebcb7404aa3d3b94 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

On Thu, Aug 11, 2011 at 8:39 AM, Don Par= ris <parrisdc@gm= ail.com> wrote:
Hi all,

Note: I'm happy to read howtos, tutorials, archived mess= ages - I just haven't found anything that addresses this yet.=A0 I foun= d a related topic on the novice list, but my post got no response there.=A0= I've been struggling with this for about a week now and need to figure= out a solution.=A0 Heck, this may not even be the best approach to hierarc= hical structures, but it sure seemed reasonable when I first read up on the= subject.=A0 Anyway...

I created a category table like so (I got the idea from a website somew= here that used it in a different way, and did not discuss much about addres= sing what I want to accomplish):
cat_id(serial)=A0 |=A0 cat_name(varchar= )=A0 |=A0 parent_id(int)=A0 |=A0 lineage(varchar)=A0 |=A0 deep(int)
1=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 r= oot_cat_a =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Null=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 1=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 1
2=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 s= ub_cat_1 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 1=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 1-2=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 2
3=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 sub_sub_cat_a=A0=A0=A0= =A0=A0=A0=A0=A0=A0 2=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0 1-2-3=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= 3

I use this to categorize transactions, and use the most appropriate=20 subcategory for any given transation item in a table called=20 trans_details.=A0 I can easily show transaction amounts by sub-category=A0= =20 (SELECT cat_name, sum(amount) AS "amount" FROM category, trans_de= tails WHERE=20 category_cat_id =3D trans_details.cat_id):

cat_name=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0 |=A0=A0=A0 amount
Transportation: Auto: Fuel= =A0=A0=A0=A0 |=A0=A0=A0=A0=A0 $100
Transportation: Auto: Maint=A0 =A0 |= =A0=A0=A0=A0=A0 $150
Transportation: Fares: Bus=A0=A0=A0 |=A0=A0=A0=A0= =A0 $40

but what I cannot figure out is how to create a summary wher= e I show
cat_name=A0=A0=A0=A0=A0 =A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 |=A0=A0=A0 am= ount
Transportation: Auto =A0=A0=A0 |=A0=A0=A0=A0=A0 $250

or, what I *rea= lly* want:
cat_name=A0=A0=A0=A0 =A0 =A0=A0=A0=A0 |=A0=A0=A0 amount
Transportation=A0=A0=A0=A0 |=A0=A0=A0=A0=A0 $290


Can anyone help= me work=20 through this?=A0 Frankly, I'm not even sure where to begin to solve the= =20 problem.=A0 I have been trying the WITH RECURSIVE feature, but I do=20 not understand very well how to apply it.=A0 The example query I have=20 brings up=20 an empty result set and I don't have a clue how I could modify it to=20 make it work.=A0 I'm not even sure it's the best method, but it see= ms like a reasonable approach.=A0 I thought about using regexp to try and match th= e initial part of the lineage to the category_id of the parents, something = like:
WHERE c1.category_id =3D 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-cat= egories (naturally) won't show up in the category_id =3D parent_id matc= hes, since such a category has no children.



That link appears to answer all of your potential questions - how to render= the hierarchy, how to find all children of a node, etc. =A0

=

--000e0cd2481cebcb7404aa3d3b94--