Sack/sql/tree-views.sql

-- items in each topic
SELECT item, topic FROM it; 


-- topic path for each topic
select name, get_topic_path( id ) from topics;


-- topic path for each item
select item_id, item_name, get_topic_path(topic_id) from it;

--
-- Select the parent nodes of a topic.
-- The parameter to get_topic_node must be hardcoded here.
-- The result tuples are ORDERED
--
SELECT t.name AS base_topic, tn_id , t2.name
FROM topics t, get_topic_node(11) g, topics t2
WHERE t.id = 11 AND t2.id = g.tn_id;


-- returns items...
SELECT it.item_id, i.name as item, it.topic_id, t.name as topic
FROM items i, topics t, item_path() it
WHERE it.item_id = i.id AND it.topic_id = t.id;

1	-- items in each topic
2	SELECT item, topic FROM it;
3
4
5	-- topic path for each topic
6	select name, get_topic_path( id ) from topics;
7
8
9	-- topic path for each item
10	select item_id, item_name, get_topic_path(topic_id) from it;
11
12	--
13	-- Select the parent nodes of a topic.
14	-- The parameter to get_topic_node must be hardcoded here.
15	-- The result tuples are ORDERED
16	--
17	SELECT t.name AS base_topic, tn_id , t2.name
18	FROM topics t, get_topic_node(11) g, topics t2
19	WHERE t.id = 11 AND t2.id = g.tn_id;
20
21
22	-- returns items...
23	SELECT it.item_id, i.name as item, it.topic_id, t.name as topic
24	FROM items i, topics t, item_path() it
25	WHERE it.item_id = i.id AND it.topic_id = t.id;
26