Mapping hierarchies to lists

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 Received on Tue Dec 11 2007 - 16:32:36 CST