duanqiang3925 2012-10-20 11:10
浏览 93
已采纳

使用php将mysql查询转换为mongodb

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

  • 写回答

2条回答 默认 最新

  • dongquan6030 2012-10-20 21:23
    关注

    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
    }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 R语言卸载之后无法重装,显示电脑存在下载某些较大二进制文件行为,怎么办
  • ¥15 java 的protected权限 ,问题在注释里
  • ¥15 这个是哪里有问题啊?
  • ¥15 关于#vue.js#的问题:修改用户信息功能图片无法回显,数据库中只存了一张图片(相关搜索:字符串)
  • ¥15 texstudio的问题,
  • ¥15 spaceclaim模型变灰色
  • ¥15 求一份华为esight平台V300R009C00SPC200这个型号的api接口文档
  • ¥15 字符串比较代码的漏洞
  • ¥15 欧拉系统opt目录空间使用100%
  • ¥15 ul做导航栏格式不对怎么改?