Re: Stuck Up In My Category Tree

On Sat, Aug 6, 2011 at 12:10 AM, Don Parris <parrisdc(at)gmail(dot)com> wrote:

> Hi all,
>
> I created a category table like so:
> 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) 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 how to apply it in my case. 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 found the below e-mail from a thread on this kind of topic (apparently in
> 2007, I only copied the relevant text). The author created a very similar
> table to mine, but talks more about how to select the child categories, not
> the root. I just don't yet have the experience to understand this well
> yet. :-)
>
> [begin copied e-mail text]
> If the data is static (or if you can get away with running a cron job
> every now and then), you can write a recursive pl/pgslq function to
> get level information for each node on the tree and assign a specific
> "incremental" node_id for each record. Due to the nature of the
> recursive function, a node_id is assigned to the children of a
> specific node instead of its siblings. You should end up with data as
> illustrated below.
>
> 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
>
> Then you can simply retrieve the children of node (N) on level (L)
> with a single statement.
>
> SELECT * FROM table WHERE node_id > N AND node_id < (SELECT node_id
> FROM table WHERE level = L AND node_id > N ORDER BY node_id LIMIT 1
> OFFSET 0);
>
> Refrain from using MIN() as performance suffers unbelievably.
> [end copied e-mail text]
>
> Thanks in advance!
> --
> 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
>
>
Could you please post a self contained test case here?

Regards,
Chetan

--
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

Website: www.enterprisedb.com
EnterpriseDB Blog : http://blogs.enterprisedb.com
Follow us on Twitter : http://www.twitter.com/enterprisedb