Join unrelated tables through a second level connected table

I have two tables of activities on a page: Views & Comments.

Views

id

timestamp

project_id

user_id

page_id

Comments

id

timestamp

project_id

user_id

page_id

comment

Pages

id

project_id

title

Now pages are related to projects:

Projects

id

account_id

title

I am trying to create a summary page that combines views and comments ordered by time (so that the most recent views/comments are at the beginning, g开发者_开发问答rouped by projects. Also, only projects for a specific account.

So the result could potentially be:

Project 1

View 5 (June 20th)

View 4 (June 18th)

Comment 5 (June 15th)

Comment 4 (June 14th)

Comment 3 (June 12th)

Project 3

View 3 (June 10th)

View 2 (June 8th)

Comment 2 (June 7th)

Project 2

View 1 (June 5th)

Comment 1 (June 4th)

If you could help with how to do this using SQL (or even doctrine) that would be awesome.

Thank you.

---

Select project_id, id, timestamp, Projects.title, 'view'  
From Views Inner Join Pages on Views.page_id = Pages.id  
Inner Join Projects on Pages.project_id = Projects.id  
UNION  
Select project_id, id, timestamp, Projects.title, 'comment'  
From Comments Inner Join Pages on Comments.page_id = Pages.id  
Inner Join Projects on Pages.project_id = Projects.id
Order By project_id, timestamp  

This should give you something like what you want. The literals at the end are so you can tell which is the view record and which is the comment. But basically, the key is the union statement.

Please let me know if I've misunderstood something.