Re: Mapping hierarchies to lists

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 13 Dec 2007 17:15:52 +0100
Message-ID: <47615AB8.9030803@gmail.com>

Marten Lehmann schrieb:
> 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

Problems of this sort are usually solved in Oracle by means of hierarchical queries aka *connect by* queries. You have to consult docs under http://tahiti.oracle.com (in particular SQL Reference) to understand, how it works, here is small example based on your data.

SQL> with t as (

   2 select 1 id,null parent,'Language' title from dual union all

   3   select 2,1,'English' from dual  union all
   4   select 3,1,'German' from dual     union all
   5   select 4,null,'Topic' from dual  union all
   6   select 5,4,'Sales' from dual  union all
   7   select 6,5,'Accounting' from dual  union all
   8   select 7,5,'Pricing' from dual  union all
   9   select 8,4,'Service' from dual  union all
  10   select 9,8,'Technical Issues' from dual  union all

  11 select 10,8,'General Questions' from dual   12 )
  13 -- End test data
  14 select id,parent,title,lpad(' ',(level-1)*3,' ')||Title tree   15 from t
  16 connect by prior id=parent
  17 start with parent is null
  18 /

         ID PARENT TITLE TREE

---------- ---------- ----------------- -------------------------
          1            Language          Language
          2          1 English              English
          3          1 German               German
          4            Topic             Topic
          5          4 Sales                Sales
          6          5 Accounting              Accounting
          7          5 Pricing                 Pricing
          8          4 Service              Service
          9          8 Technical Issues        Technical Issues
         10          8 General Questions       General Questions

Note, the part between With and -- End test data is not essential for your query - it is only to represent your test data, on your real tables you'll don't need this.
Now , how to get the id's (8,9,10):

SQL> with t as (

   2 select 1 id,null parent,'Language' title from dual union all

   3   select 2,1,'English' from dual  union all
   4   select 3,1,'German' from dual     union all
   5   select 4,null,'Topic' from dual  union all
   6   select 5,4,'Sales' from dual  union all
   7   select 6,5,'Accounting' from dual  union all
   8   select 7,5,'Pricing' from dual  union all
   9   select 8,4,'Service' from dual  union all
  10   select 9,8,'Technical Issues' from dual  union all

  11 select 10,8,'General Questions' from dual   12 )
  13 -- End test data
  14 select id
  15 from t
  16 connect by prior id=parent
  17 start with title='Service'
  18 /

         ID

---

          8
          9
         10

In addition, there is usually no need to resort to PL SQL, if task easily can be solved with SQL - mostly it is more performant solution which can be easier maintained.

Best regards

Maxim Received on Thu Dec 13 2007 - 10:15:52 CST