This select gives me the number of times a given element appears on
each of the watch lists owned by user 2;
SELECT COUNT(watch_list_id), element_id
FROM watch_list WL, watch_list_element WLE
WHERE WL.user_id = 2
AND WL.id = WLE.watch_list_id
GROUP BY WLE.element_id;
This query assumes there is only one watch list per person, and it tells
me whether or not a given item in commits_latest_ports appears on that
single watch list.
SELECT category, port,
CASE when WLE.element_id is null
then 0
else 1
END as watch
FROM watch_list_element WLE RIGHT OUTER JOIN
(
select * from commits_latest_ports
) AS TEMP
ON WLE.watch_list_id = 32
AND WLE.element_id= TEMP.element_id
ORDER BY commit_date_raw desc, category, port limit 10
My goal is to combine the two queries (i.e. allow multiple watch lists).
What I came up with works well. Can you see another solution?
select category, port, commits_latest_ports.element_id, commit_date_raw, TEMP.watch
from commits_latest_ports
LEFT OUTER JOIN
(SELECT element_id, COUNT(watch_list_id) as watch
FROM watch_list JOIN watch_list_element
ON watch_list.id = watch_list_element.watch_list_id
AND watch_list.user_id = 2
GROUP BY watch_list_element.element_id) AS TEMP
ON TEMP.element_id = commits_latest_ports.element_id
ORDER BY commit_date_raw, category, port;
She runs pretty well:
Sort (cost=1046.27..1046.27 rows=115 width=44) (actual time=6.18..6.75 rows=115
loops=1)
-> Hash Join (cost=1034.57..1042.34 rows=115 width=44) (actual time=1.94..4.88
rows=115 loops=1)
-> Seq Scan on commits_latest_ports (cost=0.00..7.15 rows=115 width=32)
(actual time=0.09..1.51 rows=115 loops=1)
-> Hash (cost=1034.55..1034.55 rows=6 width=12) (actual time=1.74..1.74
rows=0 loops=1)
-> Subquery Scan temp (cost=1034.24..1034.55 rows=6 width=12) (actual
time=1.18..1.64 rows=10 loops=1)
-> Aggregate (cost=1034.24..1034.55 rows=6 width=12) (actual
time=1.17..1.52 rows=10 loops=1)
-> Group (cost=1034.24..1034.39 rows=63 width=12) (actual
time=1.11..1.32 rows=10 loops=1)
-> Sort (cost=1034.24..1034.24 rows=63 width=12)
(actual time=1.10..1.15 rows=10 loops=1)
-> Nested Loop (cost=0.00..1032.35 rows=63
width=12) (actual time=0.64..0.97 rows=10 loops=1)
-> Index Scan using watch_list_user_id on
watch_list (cost=0.00..15.25 rows=4 width=4) (actual time=0.29..0.31 rows=3 loops=1)
-> Index Scan using
watch_list_element_pkey on watch_list_element (cost=0.00..272.63 rows=75 width=8)
(actual time=0.12..0.16 rows=3 loops=3)
Total runtime: 19.78 msec
Phew! That's fast!
--
Dan Langille : http://www.langille.org/
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
