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	dpavlin	37	-- 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