pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedStuck Up In My Own Category Tree
5+ messages / 4 participants
[nested] [flat]
* Stuck Up In My Own Category Tree
@ 2011-08-11 15:39 Don Parris <parrisdc@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Don Parris @ 2011-08-11 15:39 UTC (permalink / raw)
To: 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
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Stuck Up In My Own Category Tree
@ 2011-08-11 16:24 Samuel Gendler <sgendler@ideasculptor.com>
parent: Don Parris <parrisdc@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Samuel Gendler @ 2011-08-11 16:24 UTC (permalink / raw)
To: Don Parris <parrisdc@gmail.com>; +Cc: pgsql-sql
On Thu, Aug 11, 2011 at 8:39 AM, Don Parris <parrisdc@gmail.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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Stuck Up In My Own Category Tree
@ 2011-08-11 16:29 jasmin.dizdarevic@gmail.com
parent: Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 0 replies; 5+ messages in thread
From: jasmin.dizdarevic@gmail.com @ 2011-08-11 16:29 UTC (permalink / raw)
To: Samuel Gendler <sgendler@ideasculptor.com>; pgsql-sql-owner@postgresql.org, "Don Parris" <parrisdc@gmail.com>; +Cc: pgsql-sql
I will have do something like this soon. Look at ltree. I don't have much experience with it yet, but it seems that this ext addresses those issues.
Regards
A1 präsentiert BlackBerry® von Vodafone
-----Original Message-----
From: Samuel Gendler <sgendler@ideasculptor.com>
Sender: pgsql-sql-owner@postgresql.orgDate: Thu, 11 Aug 2011 09:24:41
To: Don Parris<parrisdc@gmail.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: [SQL] Stuck Up In My Own Category Tree
On Thu, Aug 11, 2011 at 8:39 AM, Don Parris <parrisdc@gmail.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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Stuck Up In My Own Category Tree
@ 2011-08-11 16:48 Edward W. Rouse <erouse@comsquared.com>
parent: Don Parris <parrisdc@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Edward W. Rouse @ 2011-08-11 16:48 UTC (permalink / raw)
To: 'Don Parris' <parrisdc@gmail.com>; 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
https://www.xing.com/profile/Don_Parris |
http://www.linkedin.com/in/dcparris
GPG Key ID: F5E179BE
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Stuck Up In My Own Category Tree
@ 2011-08-11 21:21 Don Parris <parrisdc@gmail.com>
parent: Edward W. Rouse <erouse@comsquared.com>
0 siblings, 0 replies; 5+ messages in thread
From: Don Parris @ 2011-08-11 21:21 UTC (permalink / raw)
To: pgsql-sql
On Thu, Aug 11, 2011 at 12:48, Edward W. Rouse <erouse@comsquared.com>wrote:
> 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. ****
>
> ** **
>
> *
> *
>
Thanks all, I will try out the suggestions. Samuel, thanks for the link!
Haven't had a chance to check it out yet, but it sure looks promising.
I'll post again when I've had a chance to look over/ try out some of these
things. :-)
Don
--
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-08-11 21:21 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-11 15:39 Stuck Up In My Own Category Tree Don Parris <parrisdc@gmail.com>
2011-08-11 16:24 ` Samuel Gendler <sgendler@ideasculptor.com>
2011-08-11 16:29 ` jasmin.dizdarevic@gmail.com
2011-08-11 16:48 ` Edward W. Rouse <erouse@comsquared.com>
2011-08-11 21:21 ` Don Parris <parrisdc@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox