Let's say I have 2 tables, Message and Product. Whenever a user post a new products or messages, users who subscribe to that particular user will have their feed updated.It is similar to Facebook feed.
The problem is how to combine the records from 2 different tables, Message and Product, to make a news feed, the feed has to be sorted by the date posted.
I think it is hard to done using purely SQL, or maybe I need another table and insert new row whenever a new product or message is created?
thanks for reading, hopefully can get some help here, thnx!!
Find common things to message and product, for example they are both type of a post, so you can have something like:
Post table has columns common to all posts; message and product tables have only columns specific to each one.
To get messages (changed after 2009-11-10 15:00)
SELECT * FROM Message AS m JOIN Post as AS p ON p.PostID = m.PostID WHERE p.LastChanged > '2009-11-10 15:00'
To get products (changed after 2009-11-10 15:00)
SELECT * FROM Product AS d JOIN Post as AS p ON p.PostID = d.PostID WHERE p.LastChanged > '2009-11-10 15:00'
To get all in one table with NULLS in columns which do not apply to message or a product.
SELECT * FROM Post AS p LEFT JOIN Message AS m p.PostID = m.PostID LEFT JOIN Product AS d ON p.PostID = d.PostID WHERE p.LastChanged > '2009-11-10 15:00'