Currently I have an mysql table like this:
+---------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| mediaID | int(11) | NO | | NULL | |
| date | datetime | NO | | NULL | |
+---------+----------+------+-----+---------+----------------+
In this table I store every hit made for a specific media. I save mediaID and date when this hit happened. So ... when I want to show trending medias (most viewed medias for specific time period), I use this mysql query:
SELECT mediaID, COUNT(*) as cnt FROM hits WHERE DATE(date) = CURDATE() - INTERVAL 1 DAY GROUP BY mediaID ORDER BY cnt DESC LIMIT 0,10
Now.. I'm planning to move this in MongoDB. Currently I have collection "hits" with the following documents:
{
_id: ObjectId("50827eaaae1c3ced6c00000f"),
mediaID: "2",
ts: ISODate("2012-10-20T13:36:26+03:00")
}
{
_id: ObjectId("50827ebeae1c3ced6c000010"),
mediaID: "1",
ts: ISODate("2012-10-20T13:36:46+03:00")
}
{
_id: ObjectId("50827ec3ae1c3c6167000008"),
mediaID: "2",
ts: ISODate("2012-10-20T13:36:51+03:00")
}
So, my question is how to convert my previous query to be able to work with MongoDB? P.S. I'm using php with php mongodb driver.
Greetings, Milen