Converting a mysql query to mongodb using php

Question

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

Asya Kamsky · Accepted Answer · 2012-10-20 21:23:31Z

2

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
}

answered Oct 20, 2012 at 21:23

Asya Kamsky

42.4k5 gold badges113 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Milen Mihalev Over a year ago

Thanks @Asya Kamsky but how can I convert it to php?

Milen Mihalev Over a year ago

Nevermind. I have able to convert it to php. Thanks a lot @asya-kamsky

Community · Accepted Answer · 2017-05-23 11:48:10Z

0

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?

edited May 23, 2017 at 11:48

CommunityBot

11 silver badge

answered Oct 21, 2012 at 19:00

Chien-Wei Huang

1,8611 gold badge18 silver badges28 bronze badges

Collectives™ on Stack Overflow

Converting a mysql query to mongodb using php

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related