I’ve been trying to figure out in what best way I can implement a stream of activities, that groups a user’s activities that were done on a specific date where the time hh:mm:ss doesn’t differ much (usually x minutes, or x seconds in difference – this can be further discussed).
These are the typical tables:
Users
id BIGINT(100) AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL
Follows
id BIGINT(100) AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT(100) NOT NULL,
following_user BIGINT(100) NOT NULL,
followed DATETIME NOT NULL
Stream
id BIGINT(100) AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT(100) NOT NULL,
verb VARCHAR(50) NOT NULL,
object_id VARCHAR(50) NOT NULL,
type VARCHAR(50) NOT NULL,
stream_date DATETIME NOT NULL
Here’s an older sqlfiddle I did: http://sqlfiddle.com/#!9/9ee94/1
My Problem:
MySQL returns things pretty well. The issue I am having is (this is an additional one) that I am trying to figure out an efficient way of grabbing from Stream and then getting the information grouped. For example:
If I have 11 Items in the DB, and I grab the first 10 updates (LIMIT) from the stream via follows table (using INNER JOINS), the problem here is as follows:
@babyBitch posted an update
19h ago
I like these sexy stuf!
@smoker posted 9 updates
@smoker posted an update
Yesterday
I am having fun, yeah! Fu***!
{…}
@smoker posted an update
Yesterday
I ate something special today! :P
Now, the last poste suggesting “Yesterday” on timestamp, should have been included in the previous output, then @smoker posted 10 updates and not 9, so does one have to run a small query on each user that someone is following to grab the right amount of data and group that by timestamp where difference in either minutes or seconds isn’t huge?
One way would be to (just what I think in my head) do this small query:
SELECT column
, column
, column FROM table WHERE id = IN ({…})
Some examples, although not exactly the same as I want:
(Here they already know the names of the users).
http://dba.stackexchange.com/questions/31515/group-activities-in-activity-feed-by-users-and-products
I hope this is clear enough, feel free to ask any question you folks might have!