3

I have what I've been told is a Bill Of Material type query - which requires a 'recursive' query. I've never heard of such a thing - and was hoping to get some help.

My schema stores asset / asset-location data. Here are the tables with some sample data ...

   location_test=# select * from assets;
    id | location_id |     name     
   ----+-------------+--------------
     1 |           4 | Mac Mini
     2 |           8 | Time Capsule

   location_test=# select * from locations order by id;
    id |      name       
   ----+-----------------
     1 | Storage Room
     2 | rack 1
     3 | shelf 1,1
     4 | container 1,1,1
     5 | container 1,1,2
     6 | container 1,1,3
     7 | shelf 1,2
     8 | container 1,2,1
     9 | container 1,2,2
    10 | container 1,2,3

The name of a location is made of a prefix (room / rack / shelf / container) followed by a comma separated list of numbers - meant to indicate what 'location' the current location is stored within. This naming convention is for clarity's sake only. The actual relation of which location is stored within which other location is represented in the 'location_maps' table (see below). An example will help clarify what I mean. There's a location called 'Storage Room'. Within 'Storage Room' - there is one rack - 'rack 1'. 'rack 1' has two shelves - 'shelf 1,1' and 'shelf 1,2' (the first '1' in '1,1' and '1,2' is meant to convey that the current shelf is contained within 'rack 1'). 'shelf 1,1' has three containers - 'container 1,1,1', 'container 1,1,2' and 'container 1,1,3' and so on.

  location_test=# select * from location_maps order by id;
   id | parent_id | location_id | level
  ----+-----------+-------------+------
    1 |           |           1 | 0
    2 |         1 |           2 | 1
    3 |         2 |           3 | 2
    4 |         3 |           4 | 3
    5 |         3 |           5 | 3
    6 |         3 |           6 | 3
    7 |         2 |           7 | 2
    8 |         7 |           8 | 3
    9 |         7 |           9 | 3
   10 |         7 |          10 | 3

The location_maps table stores the relation between a 'location' and the location in which the current location is stored - if the 'parent_id' is non-null. In addition the 'level' column stores the 'distance' from the 'root' (see ascii diagram below). If the parent_id is null - then the current location is the 'root' and is not contained in anything. The diagram ...

                                   Storage Room                                           level 0
                                        | 
                                      rack 1                               level 1
                                /                \
                               /                  \
                          shelf 1,1               shelf 1,2                level 2
                         /   |     \               
                        /    |      \                
                       /     |       \               
                      /      |        \          
                     /       |         \
                    /        |          \
     container 1,1,1  container 1,1,2   container 1,1,3                    level 3

[NOTE: the contents of 'shelf 1,2' were not included in the diagram above on purpose - due to limitations of ascii 'art']

So - for instance ...

'Storage Room' - is the 'root'

'Storage Room' contains 'rack 1'        because the location_map record with id 2 has a location_id of 2 ('rack 1') and a parent_id of 1 ('Storage Room')

'rack 1' contains 'shelf 1,1'           because the location_map record with id 3 has a location_id of 3 ('shelf 1,1') and a parent_id of 2 ('rack 1')
'shelf 1,1' contains 'container 1,1,1'  because the location_map record with id 4 has a location_id of 4 ('container 1,1,1') and a parent_id of 3 ('shelf 1,1')

and so on.

Given an Asset as in input (say the Mac Mini asset (location_id = 4)) - I'd like to be able to query the tables above and get the following result ...

    locations.id | locations.name   | level
    -------------+------------------+--------
    4            | container 1,1,1  | 3
    3            | shelf 1,1        | 2
    2            | rack 1           | 1
    1            | Storage Room     | 0

This result can then converted by my application to the following string "Storage Room : rack 1 : shelf 1,1 : container 1,1,1" - essentially a string communicating the containment hierarchy of the Mac Mini asset.

I know I can solve this by repeatedly querying my database. I'm not interested in doing that unless absolutely necessary.

Lastly - I'm using PostgreSQL version 9.3.6.

Thanks Dave

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.