Re-structuring data for Hierarchical Queries – or (Tree) Walking With Big Macs

Steve McNulty. Even the name sounds a bit hard. This is not the hero in Jason Statham’s latest celluloid exploit. Neither is it the central character in a hard-bitten cop drama.
Steve McNulty is, in fact the current Luton Town captain and a member of an endangered species – the stopper Centre-Half.
When you first set eyes on him, he looks, well, a bit chunky. You might imagine his nickname to be “Big Mac” because of his penchant for a certain fast-food chain.
This is something of a mis-conception.
Firstly, he’s not overweight. In contrast to the other players on the pitch, his body has not so much been honed to athletic perfection as hewn from solid rock.
It is a build that has not been seen for years in the elite (effete ?) Premier League.
He’s not the fastest player, as you’d expect, but he’s strong in the tackle. When he heads the ball, adjectives such as cushioning and glancing do not apply. It’s a Kirby Kiss (he’s a Scouser). The ball is definitely not his friend.
So, Big Mac he is not. He couldn’t be associated with anything that’s served with namby-pamby french-fries. A McNulty burger is a huge slab of meat wedged between two halves of a cottage loaf. It would only ever be served with chunky chips.
It’s McNulty and friends that provide the inspiration for the examples that follow.
I recently came across a situation where I needed to take some relational data and convert it into a hierarchy for the purposes of dropping it into an APEX tree. This proved slightly more challenging than I originally thought.

The Data

Our tables all have synthetic keys. There are three separate tables which make up elements of the hierarchy. There are no self-joins to walk up and down.

The tables look like this (please ignore the shonky design – they’ve been created specifically for this example) :

CREATE TABLE clubs(
  club_id NUMBER PRIMARY KEY,
  club_name VARCHAR2(50),
  home_ground VARCHAR2(50))
/

INSERT INTO clubs( club_id, club_name, home_ground)
VALUES( 1, 'LUTON TOWN', 'KENILWORTH ROAD')
/

CREATE TABLE club_positions(
  position_id NUMBER PRIMARY KEY,
  club_id NUMBER REFERENCES clubs(club_id),
  position VARCHAR2(20))
/

INSERT INTO club_positions( position_id, club_id, position)
VALUES(1, 1, 'GOALKEEPER')
/

INSERT INTO club_positions( position_id, club_id, position)
VALUES(2,1,'DEFENDER')
/

INSERT INTO club_positions( position_id, club_id, position)
VALUES(3,1,'MIDFIELDER')
/

INSERT INTO club_positions( position_id, club_id, position)
VALUES(4,1,'ATTACKER')
/

CREATE TABLE players(
  player_id NUMBER PRIMARY KEY,
  last_name VARCHAR2(50),
  position_id NUMBER REFERENCES club_positions(position_id))
/

INSERT INTO players( player_id, last_name, position_id)
VALUES(1, 'TYLER', 1)
/

INSERT INTO players( player_id, last_name, position_id)
VALUES(2, 'MCNULTY', 2)
/

INSERT INTO players( player_id, last_name, position_id)
VALUES(3, 'HOWELLS', 4)
/

INSERT INTO players( player_id, last_name, position_id)
VALUES(4, 'GRIFFITHS',2) 
/

INSERT INTO players( player_id, last_name, position_id)
VALUES(5, 'PARRY', 3)
/

INSERT INTO players( player_id, last_name, position_id)
VALUES(6, 'HENRY', 2)
/

INSERT INTO players( player_id, last_name, position_id)
VALUES(7, 'SMITH', 2)
/

INSERT INTO players( player_id, last_name, position_id)
VALUES(8, 'LAWLESS',4)
/

INSERT INTO players( player_id, last_name, position_id)
VALUES(9, 'GUTTRIDGE',3)
/

INSERT INTO players( player_id, last_name, position_id)
VALUES(10, 'STEVENSON',3)
/

INSERT INTO players( player_id, last_name, position_id)
VALUES(11, 'BENSON', 4)
/

COMMIT;

So, there is a hierarchical relationship between these three tables

Do you want fries with that ?

Do you want fries with that ?

The question is, how do we now represent this using a tree-walk ?

The in-line view

Let’s keep it simple, just retrieve the item name and the name of it’s parent…

WITH tree AS (
  SELECT club_name as item_name,
  null as parent_item
  FROM clubs
  UNION
  SELECT position as item_name,
  cl.club_name as parent_item
  FROM club_positions pos, clubs cl
  WHERE cl.club_id = pos.club_id
  UNION
  SELECT last_name as item_name,
  pos.position as parent_item
  FROM players pl, club_positions pos
  WHERE pl.position_id = pos.position_id)
SELECT lpad('-', level)||item_name as tree_node
FROM tree
START WITH parent_item IS NULL
CONNECT BY PRIOR item_name = parent_item
ORDER SIBLINGS BY item_name;

Run this and we get…

TREE_NODE
--------------------------------------------------
-LUTON TOWN
 -ATTACKER
  -BENSON
  -HOWELLS
  -LAWLESS
 -DEFENDER
  -GRIFFITHS
  -HENRY
  -MCNULTY
  -SMITH
 -GOALKEEPER
  -TYLER
 -MIDFIELDER
  -GUTTRIDGE
  -PARRY
  -STEVENSON

16 rows selected.

SQL> 

Building from the back

This is a reasonable start. However, we probably want to do a bit of re-ordering here.
We want to have the positions in the order they are on the pitch from back-to-front – i.e. Goalkeeper, Defender, Midfielder, Attacker. The players in each position, however, should still be listed in alphabetical order.

With a bit of tweaking then, we can add an additional sort criteria…

WITH tree AS (
  SELECT club_name as item_name,
  null as parent_item,
  1 as order_item
  FROM clubs
  UNION
  SELECT position as item_name,
  cl.club_name as parent_item,
  position_id as order_item
  FROM club_positions pos, clubs cl
  WHERE cl.club_id = pos.club_id
  UNION
  SELECT last_name as item_name,
  pos.position as parent_item,
  1 as order_item
  FROM players pl, club_positions pos
  WHERE pl.position_id = pos.position_id)
SELECT lpad('-', level)||item_name as tree_node
FROM tree
START WITH parent_item IS NULL
CONNECT BY PRIOR item_name = parent_item
ORDER SIBLINGS BY order_item, item_name;

So, where we need to order by the item_name, just set the order_item as 1. That way the order by clause will sort by the item_name only…

TREE_NODE
--------------------------------------------------
-LUTON TOWN
 -GOALKEEPER
  -TYLER
 -DEFENDER
  -GRIFFITHS
  -HENRY
  -MCNULTY
  -SMITH
 -MIDFIELDER
  -GUTTRIDGE
  -PARRY
  -STEVENSON
 -ATTACKER
  -BENSON
  -HOWELLS
  -LAWLESS

16 rows selected.

SQL> 

Now we’ve got our data the way we want it, the neatest way to expose this to APEX is by creating a view…

CREATE OR REPLACE VIEW player_tree_vw AS
  SELECT club_name as item_name,
  null as parent_item,
  1 as order_item
  FROM clubs
  UNION
  SELECT position as item_name,
  cl.club_name as parent_item,
  position_id as order_item
  FROM club_positions pos, clubs cl
  WHERE cl.club_id = pos.club_id
  UNION
  SELECT last_name as item_name,
  pos.position as parent_item,
  1 as order_item
  FROM players pl, club_positions pos
  WHERE pl.position_id = pos.position_id
/

Building the tree in Apex

First, we’re going to create a page or region with a type of TREE :

create_page1

I’ve accepted the defaults for Page Attributes, Tab Options, and Tree Options.

The next step is to specify the Table or view on which the tree is to be based :

choose_tree

As a starting point, we’ll specify the following for the Query :

query_setup

Choose the order_item column to order siblings :

order

For now, I’ll accept the defaults For everything else and create the Page/Region.

The result, once I’ve hit the Expand All button is reassuringly familiar :

apex_tree

If we look at the code behind it, the query APEX has generated looks like this :

tree_attributes

Obviously, to be of any use, you’ll need to add links etc but all that tree-walking malarky is already sorted.

About these ads

One thought on “Re-structuring data for Hierarchical Queries – or (Tree) Walking With Big Macs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s