Join unrelated tables through a second level connected table
- by Andy M
Hello!
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, grouped 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.