I am trying to build a 'recent events' feed and can't seem to get either my query correct, or figure out how to possible merge the results from two queries to sort them by date.
One table holds games/, and another table holds the actions of these games/.
I am trying to get the recent events to show users
1) the actions taken on games that are publicly visible (published)
2) when a new game is created and published.
So, my actions table has
actionId, gameid, userid, actiontype, lastupdate
My games table has
gameid, startDate, createdby, published, lastupdate
I currently have a query like this (simplified for easy understanding I hope).
SELECT actionId, actions.gameid, userid, actiontype, actions.lastupdate
FROM actions
JOIN
(
SELECT games.gameid, startDate, createdby, published, games.lastupdate
FROM games
WHERE
published=1 AND
lastupdate>today-2
)
publishedGames on actions.gameid=games.gameid
WHERE
actions.type IN (0,4,5,6,7) AND
actions.lastupdate>games.lastupdate and
published=1 OR
games.lastupdate>today-2 AND
published=1
This query is looking for actions from published games where the action took place after the game was published. That pretty much takes care of the first thing that needs to be shown.
However, I also need to get the results of the
SELECT games.gameid, startDate, createdby, published, games.lastupdate
FROM games
WHERE
published=1 AND
startDate>today-2
so I can include in the actions list, when a new game has been published.
When I run the query as I've got it written, I get all the actionids, and their gameids, but I don't get a row which shows the gameid when it was published.
I understand that it may be possible that I need to run two seperate queries, and then somehow merge the results afterword with php, but I'm completely lost on where to start with that as well.
