-1

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:

@babyBee posted an update
19h ago
I like these stuff!

________________________________________________
@smoker posted 9 updates

   @smoker posted an update
    Yesterday
    I am having fun, yeah!! 
    {…}
________________________________________________

@smoker posted an update
Yesterday 
I ate something special today! :P

Now, the last posts 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:

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!

  • It looks like you want something similar to Transact-SQL TOP (n) WITH TIES. The same thing could also be implemented using RANK() OVER (...). Sadly, neither feature is supported by MySQL, although there must have been attempts at workarounds, I'm sure. – Andriy M Jan 21 '16 at 17:11
  • 1
    Incidentally, I've just remembered I once had a go at it myself. I'm not very enthusiastic now about diving into that thing again, though. But in case you are willing to try and work it out and adapt it to your needs, you can find it here: dba.stackexchange.com/questions/122410/… – Andriy M Jan 21 '16 at 17:16
  • Andriy M, I am afraid that you misunderstood my problem, this has nothing to do with ranking, it's all about LIMIT on amount of selection and order by description date and hour range dynamically no input. – John Smith Jan 21 '16 at 19:26
  • Andriy M, see: stackoverflow.com/questions/8559997/… – John Smith Jan 21 '16 at 19:29
  • What is the actual issue you are trying to solve with this question then? Are you looking for ways to identify a group ("island") of activities that have happened in a short span of time? Or is this about how to include activities that belong to the last group selected with SELECT ... LIMIT n but didn't make it to the results because of the LIMIT? Both are good questions but I believe they are separate problems (and so should be asked about separately). And it's the latter one that I believe could be solved with equal ranking (provided the groups are already identified and can be sorted). – Andriy M Jan 25 '16 at 8:31
0

What is the problem? There are 11 items, you get 10, then you get 1? But you don't like that? You would prefer to simply show all 11 and be finished? If so, use LIMIT 12 (or 20 or whatever); see if you get only 11; if only 11, then show all of them; if more than (whatever), show only 10 and plan for a new page later.

Or is the problem about "clustering" on a DATETIME? That is a mathematical problem, not an SQL problem. But, again, fetch more rows than necessary, do some kind of "closeness" analysis in the application code; then decide how many items to show.

Alternatively, you could fetch only the DATETIME values, decide how many to show, then run a second SQL to fetch all the data for just the number of rows to show.

All of these allow you to say "@foo posted 10 thingies", but they don't necessarily let you say "100" or "999". Instead, say something like "@foo posted 10+ thingies yesterday". Not precise, but good enough for the context. This is solving the problem by "changing the users' expectations".

  • "What is the problem? There are 11 items, you get 10, then you get 1? But you don't like that? You would prefer to simply show all 11 and be finished? If so, use LIMIT 12 (or 20 or whatever); see if you get only 11; if only 11, then show all of them; if more than (whatever), show only 10 and plan for a new page later." Sounds pretty easy, well, that's not how it is. Each users can post anything from one item ("post") a day to 5-6 or more. So this is why it doesn't make sense to just say well LIMIT by x, because each user can post a lot or less than the other, hard to predict. – John Smith Jan 24 '16 at 18:43
  • Then, I am confused by your mention of 11 and 10. – Rick James Jan 25 '16 at 2:03
  • So let's say, the time right now is: 10:00 and between 10:00 and 11:00 (that is any minute + second, example time only, can actually be anytime). A user decides to post 3 posts and another user decides to post 5 posts. Now the idea is to** group multiple items by a particular user within a certain time-frame**, so if the time difference isn't huge but it's kept between 10:00 and 11:00 (example time only, can actually be anytime) then the items shall be grouped. – John Smith Jan 25 '16 at 8:42
  • You have to decide up front how close they need to be to be "in the same time-frame". If you pick "1 hour", then divide the time by 3600 and GROUP BY the quotient. Then you realize that 09:59 and 10:01 should be clumped together, but aren't. That leads to a messier algorithm. – Rick James Jan 25 '16 at 18:41
  • If you can't decide up front on the "cluster size" (of, say, 1 hour), you have a much more complex mathematical problem. I hope you are not asking for that; it is beyond the scope of the forum. – Rick James Jan 25 '16 at 18:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.