SELECT * FROM (
SELECT created as my_date, topic_id, 0, 0, 0, 0 FROM topic
WHERE page_type IN (1, 2)
UNION
SELECT updated as my_date, topic_id, 1, 0, 0, 0 FROM topic
WHERE date_trunc('day', updated) != created
AND page_type IN (1, 2)
UNION
SELECT e.created as my_date, subject_id, 0, 1, entry_id, subject_type
FROM entry e, topic
WHERE subject_id = topic_id AND page_type IN (1, 2)
UNION
SELECT e.created as my_date, actor_id, 0, 1, entry_id, actor_type
FROM entry e, topic
WHERE actor_id = topic_id AND page_type IN (1, 2)
UNION
SELECT e.updated as my_date, subject_id, 1, 1, entry_id, subject_type
FROM entry e, topic
WHERE date_trunc('day', e.updated) != e.created
AND subject_id = topic_id AND page_type IN (1, 2)
UNION
SELECT e.updated as my_date, actor_id, 1, 1, entry_id, actor_type
FROM entry e, topic
WHERE date_trunc('day', e.updated) != e.created
AND actor_id = topic_id AND page_type IN (1, 2)
UNION
SELECT e.created as my_date, e.topic_id, 0, 1, entry_id, rel_type
FROM topic_entry e, topic t
WHERE e.topic_id = t.topic_id AND page_type IN (1, 2)
UNION
SELECT e.updated as my_date, e.topic_id, 1, 1, entry_id, rel_type
FROM topic_entry e, topic t
WHERE e.topic_id = t.topic_id
AND date_trunc('day', e.updated) != e.created
AND page_type IN (1, 2)
) my_union
where my_union.my_date >= $dt
I would almost be tempted to create a view for each small query and name them something meaningful and then another view that does the union. It would make the queries easier to understand at least (self documented).
-Aaron
On 6/16/06, Joe <[EMAIL PROTECTED]> wrote:
Aaron Bono wrote:
> Each of your queries has the filter xxx >= $dt where the xxx is the
> first column in each select. You could simplify the query by turning
> the unioned selects into a sub-query and then putting the $dt filter in
> the outer query.
It would probably have to be two subqueries unless I can find a way to
merge the differences between new and changed rows.