PHP中的mongodb group操作实例_MongoDB【MongoDB笔记】

先数据准备

将数据插入Mongodb集合collection;集合相当于Mysql的Table
在这里插入图片描述

db.getCollection("collection").insert( {
    _id: ObjectId("5df0c634529899671d56e287"),
    id: "5761597",
    "record_id": "700f98e8-6cef-478f-ad59-3d79326e2a97",
    "user_id": "112324551",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-11 18:34:28",
    title: "告白气球",
    score1: "0.000000",
    player: "周杰伦",
    "order_id": "DC11588089828288",
} );
db.getCollection("collection").insert( {
    _id: ObjectId("5df0c634529899671d56e28a"),
    id: "5761598",
    "record_id": "700f98e8-6cef-478f-ad59-3d79326e2a97",
    "user_id": "112319823",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-11 18:34:28",
    title: "告白气球",
    score1: "0.000000",
    player: "周杰伦",
    "order_id": "DC11588089828288",
} );
db.getCollection("collection").insert( {
    _id: ObjectId("5df0c635529899671d56e28d"),
    id: "5761599",
    "record_id": "700f98e8-6cef-478f-ad59-3d79326e2a97",
    "user_id": "3294736",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-11 18:34:29",
    title: "告白气球",
    score1: "0.000000",
    player: "周杰伦",
    "order_id": "DC11588089828288",
} );
db.getCollection("collection").insert( {
    _id: ObjectId("5df1b78352989971f8160e30"),
    id: "5761646",
    "record_id": "2d6bf03d-06c6-48ec-8476-6334866c8740",
    "user_id": "112324551",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-12 11:44:03",
    title: "告白气球",
    score1: "51.861290",
    player: "周杰伦",
    "order_id": "DC12212142635393",
} );
db.getCollection("collection").insert( {
    _id: ObjectId("5df1b78352989971f8160e33"),
    id: "5761647",
    "record_id": "2d6bf03d-06c6-48ec-8476-6334866c8740",
    "user_id": "3294736",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-12 11:44:03",
    title: "告白气球",
    score1: "51.861290",
    player: "周杰伦",
    "order_id": "DC12212142635393",
} );
db.getCollection("collection").insert( {
    _id: ObjectId("5df1b78452989971f8160e36"),
    id: "5761648",
    "record_id": "2d6bf03d-06c6-48ec-8476-6334866c8740",
    "user_id": "112319823",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-12 11:44:04",
    title: "告白气球",
    score1: "51.861290",
    player: "周杰伦",
    "order_id": "DC12212142635393",
} );
db.getCollection("collection").insert( {
    _id: ObjectId("5df1b7fe529899161003532f"),
    id: "5761649",
    "record_id": "cfb15da6-4be5-48b4-829c-bed84c7bbf39",
    "user_id": "3294736",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-12 11:46:06",
    title: "告白气球",
    score1: "27.942974",
    player: "周杰伦",
    "order_id": "DC12216569151223",
} );
db.getCollection("collection").insert( {
    _id: ObjectId("5df1b7fe5298991610035332"),
    id: "5761650",
    "record_id": "cfb15da6-4be5-48b4-829c-bed84c7bbf39",
    "user_id": "112319823",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-12 11:46:06",
    title: "告白气球",
    score1: "27.942974",
    player: "周杰伦",
    "order_id": "DC12216569151223",
} );
db.getCollection("collection").insert( {
    _id: ObjectId("5df1b8e052989967c757dcf0"),
    id: "5761651",
    "record_id": "000fba24-dea1-4020-be32-56307c7e1159",
    "user_id": "3294736",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-12 11:49:52",
    title: "告白气球",
    score1: "28.129450",
    player: "周杰伦",
    "order_id": "DC12216569151223",
} );
db.getCollection("collection").insert( {
    _id: ObjectId("5df1b8e052989967c757dcf3"),
    id: "5761652",
    "record_id": "000fba24-dea1-4020-be32-56307c7e1159",
    "user_id": "112319823",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-12 11:49:52",
    title: "告白气球",
    score1: "28.129450",
    player: "周杰伦",
    "order_id": "DC12216569151223",
} );
db.getCollection("collection").insert( {
    _id: ObjectId("5df1b8e152989967c757dcf6"),
    id: "5761653",
    "record_id": "000fba24-dea1-4020-be32-56307c7e1159",
    "user_id": "112324599",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-12 11:49:53",
    title: "告白气球",
    score1: "28.129450",
    player: "周杰伦",
    "order_id": "DC12216569151223",
} );
db.getCollection("collection").insert( {
    _id: ObjectId("5df1dfd252989967cf2a22ef"),
    id: "5761665",
    "record_id": "801e9458-963d-45c4-9cce-3750a1263936",
    "user_id": "112324599",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-12 14:36:02",
    title: "告白气球",
    score1: "27.686806",
    player: "周杰伦",
    "order_id": "DC12322420857755",
} );
db.getCollection("collection").insert( {
    _id: ObjectId("5df1dfd252989967cf2a22f2"),
    id: "5761666",
    "record_id": "801e9458-963d-45c4-9cce-3750a1263936",
    "user_id": "112319823",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-12 14:36:02",
    title: "告白气球",
    score1: "27.686806",
    player: "周杰伦",
    "order_id": "DC12322420857755",
} );
db.getCollection("collection").insert( {
    _id: ObjectId("5df1dfd352989967cf2a22f5"),
    id: "5761667",
    "record_id": "801e9458-963d-45c4-9cce-3750a1263936",
    "user_id": "112318769",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-12 14:36:03",
    title: "告白气球",
    score1: "27.686806",
    player: "周杰伦",
    "order_id": "DC12322420857755",
} );
db.getCollection("collection").insert( {
    _id: ObjectId("5df1dfd352989967cf2a22f8"),
    id: "5761668",
    "record_id": "801e9458-963d-45c4-9cce-3750a1263936",
    "user_id": "112318751",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-12 14:36:03",
    title: "告白气球",
    score1: "27.686806",
    player: "周杰伦",
    "order_id": "DC12322420857755",
} );
db.getCollection("collection").insert( {
    _id: ObjectId("5df2258f5298996e4e5bb886"),
    id: "5761713",
    "record_id": "32f4b52a-2ecd-4042-8cce-1ebbfdd21273",
    "user_id": "3065878",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-12 19:33:35",
    title: "告白气球",
    score1: "27.806236",
    player: "周杰伦",
    "order_id": "DC12490227249516",
} );
db.getCollection("collection").insert( {
    _id: ObjectId("5df225905298996e4e5bb889"),
    id: "5761714",
    "record_id": "32f4b52a-2ecd-4042-8cce-1ebbfdd21273",
    "user_id": "112324669",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-12 19:33:36",
    title: "告白气球",
    score1: "27.806236",
    player: "周杰伦",
    "order_id": "DC12490227249516",
} );
db.getCollection("collection").insert( {
    _id: ObjectId("5df225905298996e4e5bb88c"),
    id: "5761715",
    "record_id": "32f4b52a-2ecd-4042-8cce-1ebbfdd21273",
    "user_id": "112324551",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-12 19:33:36",
    title: "告白气球",
    score1: "27.806236",
    player: "周杰伦",
    "order_id": "DC12490227249516",
} );
db.getCollection("collection").insert( {
    _id: ObjectId("5df32bf75298996db377c0bf"),
    id: "5761803",
    "record_id": "c4fb33e8-8f52-4a1f-b988-dd343d0a183f",
    "user_id": "3065878",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-13 14:13:11",
    title: "告白气球",
    score1: "28.435293",
    player: "周杰伦",
    "order_id": "DC13170117860737",
} );
db.getCollection("collection").insert( {
    _id: ObjectId("5df32bf75298996db377c0c2"),
    id: "5761804",
    "record_id": "c4fb33e8-8f52-4a1f-b988-dd343d0a183f",
    "user_id": "112324669",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-13 14:13:11",
    title: "告白气球",
    score1: "28.435293",
    player: "周杰伦",
    "order_id": "DC13170117860737",
} );
db.getCollection("collection").insert( {
    _id: ObjectId("5df34b39529899671d56e4ea"),
    id: "5761895",
    "record_id": "ed8c770e-3d29-4dd4-9b6f-0a01048f0e1b",
    "user_id": "112324669",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-13 16:26:33",
    title: "告白气球",
    score1: "27.078077",
    player: "周杰伦",
    "order_id": "DC13213360874927",
} );
db.getCollection("collection").insert( {
    _id: ObjectId("5df34b39529899671d56e4ed"),
    id: "5761896",
    "record_id": "ed8c770e-3d29-4dd4-9b6f-0a01048f0e1b",
    "user_id": "112324599",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-13 16:26:33",
    title: "告白气球",
    score1: "27.078077",
    player: "周杰伦",
    "order_id": "DC13213360874927",
} );
db.getCollection("collection").insert( {
    _id: ObjectId("5df34b39529899671d56e4f0"),
    id: "5761897",
    "record_id": "ed8c770e-3d29-4dd4-9b6f-0a01048f0e1b",
    "user_id": "3065878",
    file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
    timestamp: "2019-12-13 16:26:33",
    title: "告白气球",
    score1: "27.078077",
    player: "周杰伦",
    "order_id": "DC13213360874927",
} );

最终目的是title为【告白气球】根据数据中的record_id GROUP分组按分数从高到低,并且user_id不能重复
第一次group查出数据后,发现user_id也有重复的,所以再GROUP了一次。不知道你们有没有更好的办法可以交流一番。。。

先看看原生的MongoDB写法:

//colection 名为 colection ,自己据实况修改
db.collection.aggregate([
    {
        $match: {
            'title': '告白气球'
        }
    },
    {
        $group: {
            _id: {
                record_id: '$record_id'
            }
			,doc: {$first: '$$ROOT'}
            ,
            numberOfCount: {
                $sum: 1
            }
        }
    },
    {//第一次的结果排序
        $sort: {
            'doc.score1':  - 1
        }
    }
		,
	{
        $group: {
            _id: {
                group2: '$doc.user_id'							
            }
			,doc2: {$first: '$$ROOT'}
        }
    },
	{
        $sort: {
            'doc2.doc.score1':  - 1
        }
    }
		,
]);

下面是php7写法

$command = [
					'aggregate' => 'collection', //sing是你的集合
					'pipeline' => [
						[
							'$match' => [
								'title' => ['$eq' => $title],
							]
						],
						[
							'$group' => [
								'_id'  => [
									'record_id' => '$record_id',
								],
								'coun' => ['$sum' => 1],
								'doc'  => ['$first' => '$$ROOT'],
							]
						],
						[
							'$sort' => ['doc.score1' => -1]
						],
						[
							'$group' => [
								'_id'  => [
									'group2' => '$doc.user_id',
								],
								'doc2'  => ['$first' => '$$ROOT'],
							]
						],
						[
							'$sort' => ['doc2.doc.score1' => -1]
						],
						[
							'$limit' => 3
						]
					],
					'cursor' => new stdClass,
				];

				try{
					$mogoInfo = mogoConnection();//方法下面有
					$cmd = new MongoDB\Driver\Command($command);
					$cursor = $mogoInfo['manager']->executeCommand('logs', $cmd);//logs是数据库 如第一张图
					
					$temp = array();
					$userIds = array();
					foreach ($cursor as $key => $document) {
						$score = $document->doc2->doc->score1;
						$temp[$key]['id']               = $document->doc2->doc->id;
						$temp[$key]['score2']           = round($score,2);
						$temp[$key]['user_id']    		= $document->doc2->doc->user_id;
						$temp[$key]['record_id']        = $document->doc2->doc->record_id;
					}
				}catch (Exception $e){
					//处理异常
				}
//===================以上=============以上===================


//mongodb的链接方法
function mogoConnection(){
        static $manager,$writeConcern;
        $enArr	= [
            'devlop'	=>['name'=>'开发','loghost'=>'192.168.0.1:27017'],
            'product'	=>['name'=>'线上','loghost'=>'192.168.0.1:27017'],
        ];
        if(empty($manager)){
            try{
            	//链接得语法可以参考[菜鸟教程](https://www.runoob.com/mongodb/mongodb-connections.html)
                $manager		= new MongoDB\Driver\Manager("mongodb://".$enArr['devlop']['loghost']);
                //$manager = new \MongoDB\Driver\Manager("mongodb://username:password@192.168.0.1:27017");
                $writeConcern	= new MongoDB\Driver\WriteConcern(MongoDB\Driver\WriteConcern::MAJORITY, 1000);
            } catch (Exception $e) {
                return false;
            }
        }
        $data['manager'] = $manager;
        $data['writeConcern'] = $writeConcern;
        $data['MongoDBConfig'] = $enArr['develop'];
        return $data;
    }



我们可以看到其实是用了两次GROUP,并排序

主要是$commond 的写法,容易出错,自己摸索,一脸懵逼,不喜勿喷

$command = [
					'aggregate' => 'collection',
					'pipeline' => [
						[
							'$match' => [
								'title'            => ['$eq' => $title]
							]
						],
						[
							'$group' => [
								'_id'  => [
									'record_id' => '$record_id',
								],
								'coun' => ['$sum' => 1],
								'doc'  => ['$first' => '$$ROOT'],
							]
						],
						[
							'$sort' => ['doc.score1' => -1]
						],
						[
							'$limit' => 3
						]
					],
					'cursor' => new stdClass,
				];

另外插入写法

$mogoInfo = mogoConnection();//上面写的一个方法
$inLog	= [
            'field1'		=> '',
            'type'			=> '',
            'field3'		=> '',
            'datetime'		=> date('Y-m-d H:i:s'),
            'serverip'		=> '',
            'field5'		=> '',
            'time_excute'	=> '',
            'field7'		=> '',
            'field8'		=> '',
            'text'			=> '',
        ];

        $bulk	= new MongoDB\Driver\BulkWrite;
        $bulk->insert($inLog);
		try{
			$result	= $mogoInfo ['manager']->executeBulkWrite('logs.collection', $bulk, $mogoInfo['writeConcern']);	
		}catch(Exception $e){			
			//throw $e;
		}

删除Collection

		$get = $_REQUEST['collection']; // input:cccc
		$command = [
            'drop' => $get ,
        ];
        $mogoInfo = mogoConnection();
        $cmd = new MongoDB\Driver\Command($command);
        $cursor = $mogoInfo['manager']->executeCommand('logs', $cmd);

        foreach ($cursor as $document) {
            var_dump($document);
        }
//看起来虽然就几行代码;但到处找都找不到,百度了n久,简直RLG

在这里插入图片描述

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值