Distinct Query... but not so distinct

[TheFilmGod]

Hey Everyone,

 

I am creating a social networking newsfeed for one of mywebsites. One of the challenges I came upon is the query I would sent tothe database to fetch the newsfeed records for a specific user.Originally I planned on querying the database to output the records from the last five days. However, this isn't ideal because the user may have been inactive for a period of time. Instead of a basic "fetch the records for the past five days" I want to query and select all the records that occurred in the "last five days that the user was active." This means, mysql should output all the records for each of the 5 last days the user interacted on my website.

 

At first you may say, that sounds like simple solution: use DISTINCT or use LIMIT but that's not the case. For each of the 5 days, a user may have a number of records. For example, he or she may have posted on someone else's profile AND accepted a friend request from another user. That's two actions that would be saved under the same day. So a simple distinct would not output all the records for each day.

 

Here's the table:

 

newsfeed_id | user_id | datetime | all the other awesome stuff...

 

primary index > newsfeed_id

index > user_id, datetime (covering index)

 

Example dataset

 

(march 12, 2010) = shown for easier viewing; actually saved in mysql as timestamp

 

1  1  (March 11, 2010)  Data

2  1  (March 12, 2010)  Data

3  1  (March 12, 2010)  Data

4  1  (March 14, 2010)  Data

5  1  (March 14, 2010)  Data

6  1  (March 15, 2010)  Data

7  1  (March 15, 2010)  Data

8  1  (March 17, 2010)  Data

9  1  (March 17, 2010)  Data

10  1  (March 22, 2010)  Data

 

Assume today is April 13, 2010.

 

The user has been inactive since March 22, 2010. Therefore querying mysql to show all the records that occurred in the last five days (April 9-13) would be unwise. You would get no results.

 

How would I query the table to get all the records of the last five days the user was active on the website? From the example table, the select query should select all but the first record. 5 most recent days plus multiple records that may have been saved under a specific date.

[Ken2k7]

There's probably a better way to write this, but I probably don't know it nor can think of it at the moment.

 

Using table to represent your table name and dt for datetime column -

SELECT t.*
FROM table t,
     (SELECT DISTINCT u.dt 
      FROM table u 
      WHERE u.user_id = 1
      ORDER BY u.dt DESC
      LIMIT 5) n
WHERE t.user_id = 1
     AND t.dt IN (n.dt);