Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Mapping hierarchies to lists

Re: Mapping hierarchies to lists

From: <rogergorden_at_gmail.com>
Date: Wed, 12 Dec 2007 09:21:14 -0800 (PST)
Message-ID: <955c9fba-c999-48f6-a72c-4d2995c2a1bd@y5g2000hsf.googlegroups.com>


On Dec 11, 5:32 pm, Marten Lehmann <lehmannmap..._at_cnm.de> wrote:
> Hello,
>
> in our system, we want to classify messages (stored in a table) with
> certain attributes (sort them into categories). Later, we want to search
> for messages with certain attributes/categories. But don't only want to
> be able to search for specific attributes, but also for sub-categories.
>
> Let's say we have to following category tree:
>
> +- Language (1)
> | +- English (2)
> | +- German (3)
> +- Topic (4)
> +- Sales (5)
> | +- Accounting (6)
> | +- Pricing (7)
> +- Service (8)
> +- Technical Issues (9)
> +- General Questions (10)
>
> This would be stored in a table like this:
>
> id | parent | title
> ---+--------+--------------
> 1 | null | Language
> 2 | 1 | English
> 3 | 1 | German
> 4 | null | Topic
> 5 | 4 | Sales
> 6 | 5 | Accounting
> 7 | 5 | Pricing
> 8 | 4 | Service
> 9 | 8 | Technical Issues
> 10 | 8 | General Questions
>
> Now we have hundrets of support requests stored like this:
>
> id | text
> ---+----------
> 1 | I want great pricing!
> 2 | My website doesn't work.
> 3 | Wo finde ich meine Rechnung?
>
> These are linked to 0 or more attributes:
>
> message_id | attribute_id
> -----------+-------------
> 1 | 2
> 1 | 7
> 2 | 9
> 3 | 3
> 3 | 6
> 3 | 10
>
> Now how could I search for messages with a topic of the category
> "Service"? In the end, the sql query would have to look like this:
>
> select m.* from messages m, attributes a where m.id = a.message_id and
> a.attribute_id in (8, 9, 10);
>
> This should return these messages:
>
> id | text
> ---+----------
> 2 | My website doesn't work.
> 3 | Wo finde ich meine Rechnung?
>
> But getting the list "8, 9, 10" ust from the top category "Service" is
> somewhat tricky (recursion).
>
> Actually, I would like to have clean code like this:
>
> select m.* from messages m, attributes a where m.id = a.message_id and
> a.attribute_id in get_category_list(8);
>
> So get_category_list() would have to be implemented like this (pseudo code):
>
> function get_category_list ($id) {
> foreach $subcategory (select id from categories where parent = $id) {
> get_category_list($subcategory);
> }
> return $id; # add id to result list
>
> }
>
> I don't know much about PL/SQL at this time. Is it possible to write
> such a recursive function?
>
> Kind regards
> Marten

Yes it is.

Roger Gorden
Sr. DBA Skila Corp. Received on Wed Dec 12 2007 - 11:21:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US