CODEWITHSUNDEEP
phpmongodb
Milen Mihalev
Milen Mihalev

Reputation: 350

Converting a mysql query to mongodb using php

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

Upvotes: 0

Views: 1440

Answers (2)

Chien-Wei Huang
Chien-Wei Huang

Reputation: 1861

Just another advice.

Because you use int with mediaID, but the data with your mediaID in mongodb is using string, you should use intval() to convert the data before storing it into mediaID.

There are some issues(ex: comparison) if you use string instead of int. Is it possible to cast in a MongoDB-Query?

Upvotes: 0

Asya Kamsky
Asya Kamsky

Reputation: 42362

Use the Aggregation Framework. You want to only count hits from the last 24 hours, group them by mediaID and then order highest to lowest and show top ten, right?

In the shell:

today = new Date();
// this gets you yesterday but you can change this to be any time period
cutoff = new Date(today.getYear(), today.getMonth(), today.getDate()-1);
db.hits.aggregate( [
   {$match: {ts: {$gt: cutoff} } },
   {$group: {_id: "$mediaID", cnt: {$sum: 1} } },
   {$project: {mediaID: "$_id", cnt:1, _id:0 } },
   {$sort: { cnt:-1 } },
   {$limit: 10}
] )

You will get back for sample data you showed:

{
    "result" : [
        {
            "mediaID" : "2",
            "cnt" : 2
        },
        {
            "mediaID" : "1",
            "cnt" : 1
        }
    ],
    "ok" : 1
}

Upvotes: 2

Related Questions