[Yii2笔记]004 Yii2基于MongoDB构建查询和调用查询方法 - 话题

阿江 2017-09-29 20:57:12 9460次浏览 0条回复 2 0 0

说明

学习Yii Framework 2（易2框架）的过程是漫长的，也是充满乐趣的，以下是我学习Yii2框架时对官网英文资料(请参见原文网址)的代码实现，提供了较完整的代码，供你参考。不妥之处，请多多指正！

原文网址：

http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html http://www.yiiframework.com/doc-2.0/yii-mongodb-query.html

本文主题：Yii2基于MongoDB构建查询和调用查询方法

以Query Builder文档(MySQL)为主线，以Query的API文档（MongoDB）为基准，针对Mongodb进行相关测试（原SQL的很多功能在Mongodb中不能使用或使用方法都有差异） Query Builder文档(MySQL) http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html MongoDb Extension for Yii 2，V2.0.只有Query，没有Query Builder，V2.1.开始提供Query Builder Query的API文档（MongoDB）： http://www.yiiframework.com/doc-2.0/yii-mongodb-query.html

一、构建查询

$query=new \yii\mongodb\Query;

连贯操作Yii2源码文件的位置：

// all()、one()等
\vendor\yiisoft\yii2-mongodb\Query.php
// where()、select()等
\vendor\yiisoft\yii2-mongodb\Collection.php
// orderBy()、isEmpty($value)等
\vendor\yiisoft\yii2\db\QueryTrait.php

查询演示的一个完整例子

文件位置：

//D:\phpwork\basic\controllers\CountryController.php

源代码：

    public function actionQuery() {
        $query = new \yii\mongodb\Query;
        $dataProvider = $query->from('country')->all();
        return $this->render('query', ['dataProvider' => $dataProvider]);
    }

文件位置：

//D:\phpwork\basic\views\country\query.php

源代码：

<div class="country-index">
    <?php
    if($dataProvider){
        echo "<table class='table table-bordered'><tr>";
        echo "<th>#</th>";
        echo "<th>Name</th>";
        echo "<th>Population</th>";
        echo "<th>Continent</th>";
        echo "<th>Code</th>";
        echo "</tr>";
        foreach($dataProvider as $k=>$v){
            echo "<tr>";
            echo "<td>".($k+1)."</td>";
            echo "<td>".$v['name']."</td>";
            echo "<td>".$v['population']."</td>";
            echo "<td>".$v['continent']."</td>";
            echo "<td>".$v['code']."</td>";
            echo "</tr>";
        }
        echo "</table>";
    }else{
        echo "no record";
    }
    ?>
</div>

源代码：

<?
//---------------
1、select()
//select
public $this select ( array $fields )

//find()
db.getCollection('country').find({'_id':ObjectId('573e42022da5f2dc02000029')})

$query->select(['code','name'])
//$query->select('code','name')//此句无效，只能使用数组形式
//$query->select(['code as codes','name'])//此句'code as codes'无效，没有别名定义
//$query->select(['provinces','myname'=>'name','code'])//此句'myname'=>'name'无效，没有别名定义
//$query->select(['provinces',"CONCAT(name, ' ', code) AS full_name"])//此句"CONCAT(name, ' ', code) AS full_name"无效，不支持表达式，也不支持别名定义


//查询表country字段'provinces','name','code'的信息
echo "Query5:<br>";
$query=new \yii\mongodb\Query;
$customers =$query->select(['provinces','name','code'])
	->where(['_id'=>'5746e43de4017a4c0900002b'])
	->from('country')
	->one();
var_export($customers);	
/*
Query5:
array ( '_id' => MongoId::__set_state(array( '$id' => '5746e43de4017a4c0900002b', )), 'code' => '23', 'name' => '412424', 'provinces' => array ( 0 => '10', ), )
*/

//没有select语句等同于select([])，将查找出所有的记录
		echo "Query5:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query
			->where(['_id'=>'5746e43de4017a4c0900002b'])
			->from('country')
			->one();
		var_export($customers);	
/*
Query5:
array ( '_id' => MongoId::__set_state(array( '$id' => '5746e43de4017a4c0900002b', )), 'code' => '23', 'name' => '412424', 'population' => '124234', 'provinces' => array ( 0 => '10', ), 'cityInfos' => array ( 1 => array ( 'cname' => '21', ), ), )
*/

//count，获取记录总数
		echo "Query6:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query
			->from('country')
			->count();
		var_export($customers);
/*
Query6:
17
*/

//查找人口都是'1147000'的记录数：
		echo "Query6:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query
			->where(['population'=>'1147000'])
			->from('country')
			->count();
		var_export($customers);
/*
Query6:
12
*/

//查找出不重复的population总共有多少项
		echo "Query6:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query
			->from('country')
			->distinct('population');
		var_export($customers);
/*
Query6:
array ( 0 => '18886000', 1 => '1147000', 2 => '124234', 3 => '1277558000', 4 => '234234', 5 => '170115000', )
*/

//---------------
2、from()
//from
public $this from ( $collection )

//$query->from('country c')//此句无效，'country c'不能定义别名选项

3、where()
//where
public $this where ( $condition )
andWhere()
orWhere()

where() public abstract method
http://www.yiiframework.com/doc-2.0/yii-db-queryinterface.html#where()-detail

where()的两种参数格式：
hash format: ['column1' => value1, 'column2' => value2, ...]
operator format: [operator, operand1, operand2, ...]
Yii的SQL Query支持三种where()参数格式，除了以上两种之外，还有一种：
String Format（字符串格式）在MongoDB中不支持此种格式。

hash format, e.g. ['status' => 1, 'type' => 2]
operator format, e.g. ['like', 'name', 'test']

mongodb没有字符串格式的条件定义：
//$query->where('_id'='5746e43de4017a4c0900002b')//此句无效
//$query->where('population'='1147000')//此句无效

1)hash format（哈希格式）
hash format: ['column1' => value1, 'column2' => value2, ...]

['type' => 1, 'status' => 2]
(type = 1) AND (status = 2)

['id' => [1, 2, 3], 'status' => 2] 
(id IN (1, 2, 3)) AND (status = 2)

['status' => null] 
status IS NULL

//查找_id='5746e43de4017a4c0900002b'的所有记录
		echo "Query6:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query
			->from('country')
			->where(['_id'=>'5746e43de4017a4c0900002b'])
			->all();
		var_export($customers);
/*
Query6:
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '5746e43de4017a4c0900002b', )), 'code' => '23', 'name' => '412424', 'population' => '124234', 'provinces' => array ( 0 => '10', ), 'cityInfos' => array ( 1 => array ( 'cname' => '21', ), ), ), )
*/

//查找'population'='1147000'的所有记录
		echo "Query6:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select(['code'])
			->where(['population'=>'1147000'])
			->from('country')
			->all();
		var_export($customers);
/*
Query6:
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '5746e42ee4017a4c0900002a', )), 'code' => 'CA', ), 1 => array ( '_id' => MongoId::__set_state(array( '$id' => '576cb94ced91b58a8fd3f119', )), 'code' => 'C1', ), 2 => array ( '_id' => MongoId::__set_state(array( '$id' => '576cb9b5ed91b58a8fd3f11a', )), 'code' => 'C2', ), 3 => array ( '_id' => MongoId::__set_state(array( '$id' => '576cb9bced91b58a8fd3f11b', )), 'code' => 'C3', ), 4 => array ( '_id' => MongoId::__set_state(array( '$id' => '576cb9c8ed91b58a8fd3f11c', )), 'code' => 'C4', ), 5 => array ( '_id' => MongoId::__set_state(array( '$id' => '576cb9cded91b58a8fd3f11d', )), 'code' => 'C5', ), 6 => array ( '_id' => MongoId::__set_state(array( '$id' => '576cb9d3ed91b58a8fd3f11e', )), 'code' => 'C6', ), 7 => array ( '_id' => MongoId::__set_state(array( '$id' => '576cb9d8ed91b58a8fd3f11f', )), 'code' => 'C7', ), 8 => array ( '_id' => MongoId::__set_state(array( '$id' => '576cb9e0ed91b58a8fd3f120', )), 'code' => 'C8', ), 9 => array ( '_id' => MongoId::__set_state(array( '$id' => '576cb9e6ed91b58a8fd3f121', )), 'code' => 'C9', ), 10 => array ( '_id' => MongoId::__set_state(array( '$id' => '576cb9eced91b58a8fd3f122', )), 'code' => 'C0', ), 11 => array ( '_id' => MongoId::__set_state(array( '$id' => '576cc65ced91b58a8fd3f123', )), 'code' => 'C0', ), )
*/

//查找符合条件的记录
		echo "Query6:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select(['name'])
			->from('country')
			//code in('C0','CA','C3') and  population='1147000'
			->where(['code'=>['C0','CA','C3'],'population'=>'1147000'])
			->all();
		var_export($customers);
/*
Query6:
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '5746e42ee4017a4c0900002a', )), 'name' => 'Canada', ), 1 => array ( '_id' => MongoId::__set_state(array( '$id' => '576cb9bced91b58a8fd3f11b', )), 'name' => 'Canada3', ), 2 => array ( '_id' => MongoId::__set_state(array( '$id' => '576cb9eced91b58a8fd3f122', )), 'name' => 'Canada0', ), 3 => array ( '_id' => MongoId::__set_state(array( '$id' => '576cc65ced91b58a8fd3f123', )), 'name' => 'Canada0', ), )
*/

//查找codes已定义的所有记录//not null:
		echo "Query6:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select(['code','codes'])
			->from('country')
			->where(['<>','codes',null])
			->all();
		var_export($customers);
/*
Query6:
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '576cb9e6ed91b58a8fd3f121', )), 'code' => 'C9', 'codes' => '123', ), )
*/

//统计codes没有定义的记录总数//null
		echo "Query6:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query
			->from('country')
			->where(['codes'=>null])
			->count();
		var_export($customers);
/*
Query6:
16
*/

2)operator format(操作符格式)
operator format: [operator, operand1, operand2, ...]
//文件位置
\vendor\yiisoft\yii2-mongodb\Collection.php
操作符：
and
or
not
between
not between
in
not in
regex
like
> gt
< lt
>=  gte
<=	lte
<>	ne
!=	ne
=	eq
==	eq

/*以下几个是没有的：
or like
not like
or not like
exists
not exists
*/

//and
['and',[array1...]]
//实例,and,population是int32类型
		echo "Query7:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select([])
			->from('country')
			//code in('C3','C4') and population=>76 and _id="5777b2682da5f2c81000002b"
			->where(['code'=>['C3','C4'],'population'=>76,"_id"=>"5777b2682da5f2c81000002b"])
			//->where(['and',['code'=>['C3','C4'],'population'=>76,"_id"=>"5777b2682da5f2c81000002b"]])//与上句等效
			//->where(['and',['code'=>['C3','C4']],['population'=>76],["_id"=>"5777b2682da5f2c81000002b"]])//与上句等效
			->all();
		echo count($customers)." <br>".var_export($customers,true);
/*
Query7:
1 
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b2682da5f2c81000002b', )), 'code' => 'C4', 'name' => 'Country4', 'continent' => 'Asia', 'population' => 76, ), )
*/
//错误写法，'population'是int32类型，应写为'population'=>76
//->where(['and',['code'=>['C3','C4'],'population'=>'76',"_id"=>"5777b2682da5f2c81000002b"]])//错误写法

//错误写法，查询条件中有一个参数使用的是操作符格式，则其他的几个参数也必须使用数组形式括起来，并使用'and'进行连接
//->where([['in','code',['C3','C4','C5']],'population'=>1147000,"_id"=>"576cb9bced91b58a8fd3f11b"])//错误写法
//->where([['in','code',['C3','C4','C5']],['population'=>1147000],["_id"=>"576cb9bced91b58a8fd3f11b"]])//错误写法
//正确写法：(如果有非"=>"操作符，则必须使用'and'格式)
->where(['and',['in','code',['C3','C4','C5']],['population'=>1147000],["_id"=>"576cb9bced91b58a8fd3f11b"]])

//or ,population是int32
->where(['or',['code'=>['C3','CN']],['population'=>1147000]])
code in('C3','CN') or population=1147000

->where(['or',['code'=>['C3','CN'],'population'=>1147000],["_id"=>"5746e448e4017a4c0900002c"]])
(code in('C3','CN') and population=1147000) or _id="5746e448e4017a4c0900002c"

//实例 or
		echo "Query6:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select([])
			->from('country')
			//code in('C3','C4') or population=>88 or _id="5777b2cf2da5f2981000002d"
			->where(['or',['code'=>['C3','C4']],['population'=>88],["_id"=>"5777b2cf2da5f2981000002d"]])
			->all();
		echo count($customers)." <br>".var_export($customers,true);
/*
Query6:
4 
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b1d52da5f22408000029', )), 'code' => 'C0', 'name' => 'Country0', 'continent' => 'Europe', 'population' => 88, ), 1 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b2562da5f2c81000002a', )), 'code' => 'C3', 'name' => 'Country3', 'continent' => 'Ocean', 'population' => 20, ), 2 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b2682da5f2c81000002b', )), 'code' => 'C4', 'name' => 'Country4', 'continent' => 'Asia', 'population' => 76, ), 3 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b2cf2da5f2981000002d', )), 'code' => 'C9', 'name' => 'Country9', 'continent' => 'Ocean', 'population' => 62, ), )
*/
//错误写法：[]内的一维数组默认就是'and'关系，应该每个条件写一个数组，前面的or才能生效
//->where(['or',['code'=>['C3','C4'],'population'=>88,"_id"=>"5777b2cf2da5f2981000002d"]])
//如下句，'or'是摆设，没有生效
->where(['or',['code'=>['C3','C4'],'population'=>76,"_id"=>"5777b2682da5f2c81000002b"]])
SQL:code in('C3','C4') and population=>76 and _id="5777b2682da5f2c81000002b"
/*
Query6:
1 
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b2682da5f2c81000002b', )), 'code' => 'C4', 'name' => 'Country4', 'continent' => 'Asia', 'population' => 76, ), )
*/

//(and) or (and)//andorand

//实例，(and) or (and)
		echo "Query6:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select([])
			->from('country')
			//(code in('C3','C4') and population=20) or (name="234324" and continent="Asia")
			->where(['or',['code'=>['C3','C4'],'population'=>20],["name"=>"234324","continent"=>"Asia"]])
			->all();
		echo count($customers)." <br>".var_export($customers,true);
/*
Query9:
3 
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e34632da5f22c0200002a', )), 'code' => 'Aa', 'name' => '234324', 'population' => 23444234, 'continent' => 'Asia', ), 1 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e34802da5f2a00200002a', )), 'code' => 'Ba', 'name' => '234324', 'population' => 23444234, 'continent' => 'Asia', ), 2 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b2562da5f2c81000002a', )), 'code' => 'C3', 'name' => 'Country3', 'continent' => 'Ocean', 'population' => 20, ), )
*/
//错误写法：'population'=>'20',population是int32
//->where(['or',['code'=>['C3','C4'],'population'=>'20'],["_id"=>"5777b2cf2da5f2981000002d"]])//错误写法

//(or) and (or)//orandor

//实例,(or) and (or)
		echo "Query9:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select([])
			->from('country')
			//(continent="Europe" or continent="Ocean") and (population=88 or population=62)
			->where(["and",['or',["continent"=>"Europe"],["continent"=>"Ocean"]],['or',["population"=>88],["population"=>62]]])
			->all();
		echo count($customers)." <br>".var_export($customers,true);
/*
Query9:
2 
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b1d52da5f22408000029', )), 'code' => 'C0', 'name' => 'Country0', 'continent' => 'Europe', 'population' => 88, ), 1 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b2cf2da5f2981000002d', )), 'code' => 'C9', 'name' => 'Country9', 'continent' => 'Ocean', 'population' => 62, ), )
*/

//实例,(or) and (or)的两个or子例：
//子例1
		echo "Query9:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select([])
			->from('country')
			//continent="Europe" or continent="Ocean"
			->where(['or',["continent"=>"Europe"],["continent"=>"Ocean"]])
			->all();
		echo count($customers)." <br>".var_export($customers,true);
/*
Query9:
4 
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b1d52da5f22408000029', )), 'code' => 'C0', 'name' => 'Country0', 'continent' => 'Europe', 'population' => 88, ), 1 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b1e72da5f2240800002a', )), 'code' => 'C1', 'name' => 'Country1', 'continent' => 'Europe', 'population' => 98, ), 2 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b2562da5f2c81000002a', )), 'code' => 'C3', 'name' => 'Country3', 'continent' => 'Ocean', 'population' => 20, ), 3 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b2cf2da5f2981000002d', )), 'code' => 'C9', 'name' => 'Country9', 'continent' => 'Ocean', 'population' => 62, ), )
*/

//子例2
		echo "Query9:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select([])
			->from('country')
			//population=88 or population=62
			->where(['or',["population"=>88],["population"=>62]])
			->all();
		echo count($customers)." <br>".var_export($customers,true);
/*
Query9:
5 
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e342e2da5f22c02000029', )), 'code' => 'aA', 'name' => 'we234df', 'population' => 62, 'continent' => 'Asia', ), 1 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e42022da5f2dc02000029', )), 'code' => '99', 'name' => '99999', 'population' => 62, 'provinces' => array ( 0 => '10', ), 'continent' => 'Asia', ), 2 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e432d2da5f2b8ee000029', )), 'code' => 'sd', 'name' => '2343424', 'population' => 88, 'provinces' => array ( 0 => '10', 1 => '13', 2 => '14', 3 => '15', 4 => '16', 5 => '18', 6 => '19', ), 'cityInfos' => array ( 1 => array ( 'cname' => '21', 'zipcode' => '21', ), 2 => array ( 'cname' => '22', 'zipcode' => '22', ), 3 => array ( 'cname' => '23', 'zipcode' => '23', ), 4 => array ( 'cname' => '24', ), 5 => array ( 'cname' => '25', ), ), 'continent' => 'Asia', ), 3 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b1d52da5f22408000029', )), 'code' => 'C0', 'name' => 'Country0', 'continent' => 'Europe', 'population' => 88, ), 4 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b2cf2da5f2981000002d', )), 'code' => 'C9', 'name' => 'Country9', 'continent' => 'Ocean', 'population' => 62, ), )
*/


//not 
['not', ['attribute' => null]] 
NOT (attribute IS NULL)

//实例：not的用法
		echo "Query1:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select([])
			->from('country')
			->where(['not','code','C0'])
			->all();
		echo count($customers)." <br>".var_export($customers,true);
/*
Query1:
19 
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e2bf32da5f2d4ff00002b', )), 'code' => 'CD', 'name' => 'crsdfsfd', 'population' => 234234, 'continent' => 'Asia', ), 1 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e2c4e2da5f20803000029', )), 'code' => 'AA'
......
*/

//查找所有codes已定义的记录//not null 
		echo "Query9:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select([])
			->from('country')
			->where(['<>','codes',null])
			//->where(['!=','codes',null])//与上句等效
			->all();
		echo count($customers)." <br>".var_export($customers,true);
/*
Query9:
1 
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b2cf2da5f2981000002d', )), 'code' => 'C9', 'name' => 'Country9', 'continent' => 'Ocean', 'population' => 62, 'codes' => '156', ), )
*/
//错误写法：
//->where(['not',['codes'=>null]])//此句无法运行
//->where(['not',['codes','CA']])//此句无法运行
//->where(['not','codes',null])//此句无法运行

//not in//notin 
//实例，not in
		echo "Query1:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select([])
			->from('country')
			->where(['not in','code',['C0','C1']])
			->all();
		echo count($customers)." <br>".var_export($customers,true);
/*
Query1:
18 
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e2bf32da5f2d4ff00002b', )), 'code' => 'CD', 'name' => 'crsdfsfd', 'population' => 234234, 'continent' => 'Asia', ), 1 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e2c4e2da5f20803000029', )), 'code' => 'AA', 'name' => 'AAWErrsdfd', 'population' => 2324324, 'continent' => 'Asia', ), 2 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e33ba2da5f2a002000029', )), 'code' => 'aa', 'name' => '2342', 'population' => 234324,
......
*/

//in 
//实例，in
		echo "Query1:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select([])
			->from('country')
			->where(['in','code',['C0','C1']])
			->all();
		echo count($customers)." <br>".var_export($customers,true);
/*
Query1:
2 
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b1d52da5f22408000029', )), 'code' => 'C0', 'name' => 'Country0', 'continent' => 'Europe', 'population' => 88, ), 1 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b1e72da5f2240800002a', )), 'code' => 'C1', 'name' => 'Country1', 'continent' => 'Europe', 'population' => 98, ), )
*/

//between
between可以用于int,dobule,date等类型（MongoDB数据类型）
//
		echo "Query6:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select(['name','population'])
			->from('country')
			//population>1 and population<100
			->where(['between','population',1,100])
			->all();
		echo count($customers)." <br>".var_export($customers,true);
/*
Query6:
3 
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '576cb94ced91b58a8fd3f119', )), 'name' => 'Canada1', 'population' => 78, ), 1 => array ( '_id' => MongoId::__set_state(array( '$id' => '576cb9b5ed91b58a8fd3f11a', )), 'name' => 'Canada2', 'population' => 88, ), 2 => array ( '_id' => MongoId::__set_state(array( '$id' => '576cb9d3ed91b58a8fd3f11e', )), 'name' => 'Canada6', 'population' => 56, ), )
*/

//not between(一个无法正确执行的查询命令)
//下句是无法正常执行的，搜索结果为空，可能是yii2的mongodb插件将语句错误解析为 and，实则应为 or)：
//->where(['not between','population',20,100000000])//此句不能正常执行
//population<20 and population>100000000 //错误
//population<20 or population>100000000 //正确
/*
Query6:
0 
array ( )
*/
//实例，not between等效的正确写法
		echo "Query6:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select(['name','population'])
			->from('country')
			//population<20 or population>100000000
			->where(['or',['<','population',20],['>','population',100000000]])
			->all();
		echo count($customers)." <br>".var_export($customers,true);
/*
Query6:
5 
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e342e2da5f22c02000029', )), 'name' => 'we234df', 'population' => 234324234, ), 1 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e3b182da5f22c0200002b', )), 'name' => '234234', 'population' => 2147483647, ), 2 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e42ab2da5f2dc0200002a', )), 'name' => '22232234', 'population' => 2147483647, ), 3 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b23e2da5f2c810000029', )), 'name' => 'Country2', 'population' => 18, ), 4 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b27a2da5f29810000029', )), 'name' => 'Country5', 'population' => 18, ), )
*/

//like
//实例，like，不区分大小写
		echo "Query1:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select([])
			->from('country')
			->where(['like','code','c'])
			->all();
		echo count($customers)." <br>".var_export($customers,true);
/*
Query1:
12 
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e2bf32da5f2d4ff00002b', )), 'code' => 'CD', 'name' => 'crsdfsfd', 'population' => 234234, 'continent' => 'Asia', ), 1 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e3b182da5f22c0200002b', )), 'code' => 'cd', 'name' => '234234', 'population' => 2147483647, 'provinces' => array ( 0 => '10', 1 => '11', 2 => '12', 3 => '17', 4 => '18', 5 => '19', ), 'continent' => 'Asia', ), 2 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b1d52da5f22408000029', )), 'code' => 'C0', 'name' => 
......
*/

//regex
//实例，//正则表达式搜索
		echo "Query1:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select([])
			->from('country')
			//查找'name'以'country'开头的所有记录
			->where(['regex','name','/country[\w]+/i'])
			//查找'name'以'country'开头的带一个数字的所有记录
			->where(['regex','name','/country[\d]{1}?/i'])
			->all();
		echo count($customers)." <br>".var_export($customers,true);
/*
Query1:
10 
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b1d52da5f22408000029', )), 'code' => 'C0', 'name' => 'Country0', 'continent' => 'Europe', 'population' => 88, ), 1 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b1e72da5f2240800002a', )), 'code' => 'C1', 'name' => 'Country1', 'continent' => 'Europe', 'population' => 98, ), 2 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b23e2da5f2c810000029', )), 'code' => 'C2', 'name' => 'CountryNP', 'continent' => 'Asia', 'population' => 18, ), 3 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b2562da5f2c81000002a', )), 'code' => 'C3', 'name' => 'Country3', 'continent' => 'Ocean', 'population' => 20, ), 4 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b2682da5f2c81000002b', )), 'code' => 'C4', 'name' => 'Country4', 'continent' => 'Asia', 'population' => 76, ), 5 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b27a2da5f29810000029', )), 'code' => 'C5', 'name' => 'Country5', 'continent' => 'Asia', 'population' => 18, ), 6 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b28c2da5f2981000002a', )), 'code' => 'C6', 'name' => 'Country6', 'continent' => 'Asia', 'population' => 20, ), 7 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b29b2da5f2981000002b', )), 'code' => 'C7', 'name' => 'Country7', 'continent' => 'Asia', 'population' => 685, ), 8 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b2bd2da5f2981000002c', )), 'code' => 'C8', 'name' => 'Countryhoo', 'continent' => 'Aisa', 'population' => 55, ), 9 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b2cf2da5f2981000002d', )), 'code' => 'C9', 'name' => 'Country9', 'continent' => 'Ocean', 'population' => 62, 'codes' => '156', ), )
*/
//查找'name'以'country'开头的带一个数字的所有记录
->where(['regex','name','/country[\d]{1}?/i'])
/*
Query5:
7 
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b1d52da5f22408000029', )), 'code' => 'C0', 'name' => 'Country0', 'continent' => 'Europe', 'population' => 88, ), 1 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b1e72da5f2240800002a', )), 'code' => 'C1', 'name' => 'Country1', 'continent' => 'Europe', 'population' => 98, ), 2 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b2562da5f2c81000002a', )), 'code' => 'C3', 'name' => 'Country3', 'continent' => 'Ocean', 'population' => 20, ), 3 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b2682da5f2c81000002b', )), 'code' => 'C4', 'name' => 'Country4', 'continent' => 'Asia', 'population' => 76, ), 4 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b27a2da5f29810000029', )), 'code' => 'C5', 'name' => 'Country5', 'continent' => 'Asia', 'population' => 18, ), 5 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b29b2da5f2981000002b', )), 'code' => 'C7', 'name' => 'Country7', 'continent' => 'Asia', 'population' => 685, ), 6 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b2cf2da5f2981000002d', )), 'code' => 'C9', 'name' => 'Country9', 'continent' => 'Ocean', 'population' => 62, 'codes' => '156', ), )
*/

// 正则表达式搜索
    public function validateUserName($attribute, $params){
        //连接服务器，并选择一个数据库imalldb-Prev
/*        $m = new \MongoClient('mongodb://prev:123456@120.27.131.101:27017/imalldb-Prev');
        $dbname='imalldb-Prev';
        $db=$m->$dbname;
        $collection = $db->accounts_frontend; // 选择集合*/
        $collection=static::getCollection();
        $regex = new \MongoRegex('/^'.$this->$attribute.'$/i');
        $haveRec = $collection->find(['userName' =>$regex]);
        if($haveRec->count()>0){
            $this->addError($attribute,Yii::t('app', 'Login account')." '".$this->userName."' ".Yii::t('app','have exists')."!");
        }
    }


//> gt
//实例，大于
		echo "Query6:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select([])
			->from('country')
			//population > 100000000
			->where(['>=','population',23444234])
			->all();
		echo count($customers)." <br>".var_export($customers,true);
/*
Query5:
2 
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e3b182da5f22c0200002b', )), 'code' => 'cd', 'name' => '234234', 'population' => 2147483647, 'provinces' => array ( 0 => '10', 1 => '11', 2 => '12', 3 => '17', 4 => '18', 5 => '19', ), 'continent' => 'Asia', ), 1 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e42ab2da5f2dc0200002a', )), 'code' => '22', 'name' => '22232234', 'population' => 2147483647, 'provinces' => array ( 0 => '10', ), 'continent' => 'Asia', ), )
*/

//< lt
//实例，小于
		echo "Query5:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select([])
			->from('country')
			//population < 20
			->where(['<','population',20])
			->all();
		echo count($customers)." <br>".var_export($customers,true);
/*
Query5:
2 
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b23e2da5f2c810000029', )), 'code' => 'C2', 'name' => 'CountryNP', 'continent' => 'Asia', 'population' => 18, ), 1 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b27a2da5f29810000029', )), 'code' => 'C5', 'name' => 'Country5', 'continent' => 'Asia', 'population' => 18, ), )
*/

//>=  gte
//实例，大于等于
		echo "Query6:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select([])
			->from('country')
			//population >= 23444234
			->where(['>=','population',23444234])
			->all();
		echo count($customers)." <br>".var_export($customers,true);
/*
Query6:
4 
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e34632da5f22c0200002a', )), 'code' => 'Aa', 'name' => '234324', 'population' => 23444234, 'continent' => 'Asia', ), 1 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e34802da5f2a00200002a', )), 'code' => 'Ba', 'name' => '234324', 'population' => 23444234, 'continent' => 'Asia', ), 2 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e3b182da5f22c0200002b', )), 'code' => 'cd', 'name' => '234234', 'population' => 2147483647, 'provinces' => array ( 0 => '10', 1 => '11', 2 => '12', 3 => '17', 4 => '18', 5 => '19', ), 'continent' => 'Asia', ), 3 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e42ab2da5f2dc0200002a', )), 'code' => '22', 'name' => '22232234', 'population' => 2147483647, 'provinces' => array ( 0 => '10', ), 'continent' => 'Asia', ), )
*/

//<=	lte
//实例，小于等于
		echo "Query5:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select([])
			->from('country')
			//population <= 20
			->where(['<=','population',20])
			->all();
		echo count($customers)." <br>".var_export($customers,true);
/*
Query5:
4 
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b23e2da5f2c810000029', )), 'code' => 'C2', 'name' => 'CountryNP', 'continent' => 'Asia', 'population' => 18, ), 1 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b2562da5f2c81000002a', )), 'code' => 'C3', 'name' => 'Country3', 'continent' => 'Ocean', 'population' => 20, ), 2 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b27a2da5f29810000029', )), 'code' => 'C5', 'name' => 'Country5', 'continent' => 'Asia', 'population' => 18, ), 3 => array ( '_id' => MongoId::__set_state(array( '$id' => '5777b28c2da5f2981000002a', )), 'code' => 'C6', 'name' => 'Country', 'continent' => 'Asia', 'population' => 20, ), )
*/

//<>	ne//neq
//!=	ne

//_id查询时只能使用'not in'，不能使用'!='或'<>'
		$where=['and',['isDeleted'=>false],['title'=>$this->$attribute]];
        if(!$this->isNewRecord){

            $where[]=['not in','_id',$this->id];
        }

//实例，不等于
		echo "Query6:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select([])
			->from('country')
			->where(['<>','population',23444234])
			//->where(['!=','population',23444234])//与上句同效
			->all();
		echo count($customers)." <br>".var_export($customers,true);
/*
Query6:
18 
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e2bf32da5f2d4ff00002b', )), 'code' => 'CD', 'name' => 'crsdfsfd', 'population' => 234234, 'continent' => 'Asia', ), 1 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e2c4e2da5f20803000029', )), 'code' => 'AA', 'name' => 'AAWErrsdfd', 'population' => 2324324, 'continent' => 'Asia', ), 2 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e33ba2da5f2a002000029', )), 'code' => 'aa', 'name' => '2342', 'population' => 234324, 'continent' => 'Asia', ), 3 => array ( '_id' => MongoId::__set
......
*/
        


//=	eq
//==	eq
//实例，等于
		echo "Query7:<br>";
		$query=new \yii\mongodb\Query;
		$customers =$query->select([])
			->from('country')
			->where(['=','population',23444234])
			//->where(['==','population',23444234])//与上句同效
			->all();
		echo count($customers)." <br>".var_export($customers,true);
/*
Query7:
2 
array ( 0 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e34632da5f22c0200002a', )), 'code' => 'Aa', 'name' => '234324', 'population' => 23444234, 'continent' => 'Asia', ), 1 => array ( '_id' => MongoId::__set_state(array( '$id' => '573e34802da5f2a00200002a', )), 'code' => 'Ba', 'name' => '234324', 'population' => 23444234, 'continent' => 'Asia', ), )
*/

// getCollection()
//实例，getCollection()
		echo "Query2:<br>";
		$query=new \yii\mongodb\Query();
		$customers =$query->from('country');
		echo var_export($query->getCollection(),true);
/*
Query6:
yii\mongodb\Collection::__set_state(array( 'mongoCollection' => MongoCollection::__set_state(array( 'w' => 1, 'wtimeout' => 10000, )), ))
*/


//modify
//实例，modify(),存储数据
		$id="576cc65ced91b58a8fd3f123";
		echo "Query5:<br>";
		$query=new \yii\mongodb\Query;
		$query->from('country');
		$customers =$query->select(['provinces','name','code','area','population'])
			->where(['_id'=>$id])
			->from('country')
			->one();
		$areas=$customers["area"];
		foreach($areas as $key=>$area){
			echo $key." : ".$area["stateAbbrName"]." ".$area["stateFullName"]."<br>";
		}
		//var_dump($customers);
		if(!empty($customers)){
			$customers["population"]=158;
			$customers["name"]='Canada05';
			$new=$query->where(['_id'=>$id])
				->modify($customers);
			//默认输出的$new是修改之前的数据：'population' => 688, 'name' => 'Canada08'
			echo "<br>done:<br>".var_export($new,true);
		}else{
			echo "<br>empty";
		}
/*
Query5:
0 : VA Virginia
1 : TX Texas
2 : ID Idaho
3 : FL Florida
4 : UT Utah
5 : SD South Dakota

done:
array ( '_id' => MongoId::__set_state(array( '$id' => '576cc65ced91b58a8fd3f123', )), 'code' => 'CL', 'provinces' => array ( 0 => '10', ), 'area' => array ( 0 => array ( 'country' => 'US', 'stateAbbrName' => 'VA', 'stateFullName' => 'Virginia', 'cityInfos' => array ( 0 => array ( 'name' => 'VERONA', 'zipcodes' => array ( 0 => '24482', ), ), ), ), 1 => array ( 'country' => 'US', 'stateAbbrName' => 'TX', 'stateFullName' => 'Texas', 'cityInfos' => array ( 0 => array ( 'name' => 'WALLER', 'zipcodes' => array ( 0 => '77484', ), ), ), ), 2 => array ( 'country' => 'US', 'stateAbbrName' => 'ID', 'stateFullName' => 'Idaho', 'cityInfos' => array ( 0 => array ( 'name' => 'NAMPA', 'zipcodes' => array ( 0 => '83651', ), ), ), ), 3 => array ( 'country' => 'US', 'stateAbbrName' => 'FL', 'stateFullName' => 'Florida', 'cityInfos' => array ( 0 => array ( 'name' => 'LOWELL', 'zipcodes' => array ( 0 => '32663', ), ), ), ), 4 => array ( 'country' => 'US', 'stateAbbrName' => 'UT', 'stateFullName' => 'Utah', 'cityInfos' => array ( 0 => array ( 'name' => 'BOUNTIFUL', 'zipcodes' => array ( 0 => '84010', ), ), ), ), 5 => array ( 'country' => 'US', 'stateAbbrName' => 'SD', 'stateFullName' => 'South Dakota', 'cityInfos' => array ( 0 => array ( 'name' => 'CLAIRECITY', 'zipcodes' => array ( 0 => '57224', ), ), ), ), ), 'population' => 688, 'name' => 'Canada08', )
*/

//实例，modify()，存储数据，返回更新后的数据
		$id="576cc65ced91b58a8fd3f123";
		echo "Query5:<br>";
		$query=new \yii\mongodb\Query;
		$query->from('country');
		$customers =$query
			->where(['_id'=>$id])
			->from('country')
			->one();
		//var_dump($customers);
		if(!empty($customers)){
			$customers["population"]=80;
			$customers["name"]='Canada01';
			$options['new']=true;//返回更新后的数据
			$new=$query->where(['_id'=>$id])
				->modify($customers,$options);
			echo "<br>done:<br>".var_export($new,true);
		}else{
			echo "<br>empty";
		}
/*
Query5:
done:
array ( '_id' => MongoId::__set_state(array( '$id' => '576cc65ced91b58a8fd3f123', )), 'provinces' => array ( 0 => '10', ), 'population' => 80, 'name' => 'Canada01', )
*/

//实例，modify()，修改子记录信息//updateChildRecord
		$id="576cc65ced91b58a8fd3f123";
		echo "Query5:<br>";
		$query=new \yii\mongodb\Query;
		$query->from('country');
		$customers =$query->select(['provinces','name','code','area','population'])
			->where(['_id'=>$id])
			->from('country')
			->one();
		$areas=$customers["area"];
		foreach($areas as $key=>$area){
			echo $key." : ".$area["stateAbbrName"]." ".$area["stateFullName"]."<br>";
		}
		if(!empty($customers)){
			//修改子记录信息
			//准备子记录数据
			$newArea["country"]="US";
			$newArea["stateAbbrName"]="XA";
			$newArea["stateFullName"]="XeAni";
			$newArea["cityInfos"]["name"]="NAMPA";
			$newArea["cityInfos"]["zipcodes"][0]="89232";
			$index=2;//子记录索引号
			$key="ANGULAR";//子记录比对的关键词，以防止修改错误的发生
			if($customers["area"][$index]["cityInfos"]["name"]==$key){
				//比对成功后进行记录修改
				$customers["area"][$index]=$newArea;//更新指定index的记录值
				$options["new"]=true;
				$new=$query->where(['_id'=>$id])
					->modify($customers,$options);
				echo "<br>done:<br>".var_export($new,true);
			}
		}else{
			echo "<br>empty";
		}
/*
Query5:
0 : VA Virginia
1 : TX Texas
2 : AX AniXe
3 : FL Florida
4 : UT Utah
5 : SD South Dakota

done:
array ( '_id' => MongoId::__set_state(array( '$id' => '576cc65ced91b58a8fd3f123', )), 'provinces' => array ( 0 => '10', ), 'population' => 182, 'name' => 'Canada08', 'code' => 'CL', 'area' => array ( 0 => array ( 'country' => 'US', 'stateAbbrName' => 'VA', 'stateFullName' => 'Virginia', 'cityInfos' => array ( 0 => array ( 'name' => 'VERONA', 'zipcodes' => array ( 0 => '24482', ), ), ), ), 1 => array ( 'country' => 'US', 'stateAbbrName' => 'TX', 'stateFullName' => 'Texas', 'cityInfos' => array ( 0 => array ( 'name' => 'WALLER', 'zipcodes' => array ( 0 => '77484', ), ), ), ), 2 => array ( 'country' => 'US', 'stateAbbrName' => 'AX', 'stateFullName' => 'AniXe', 'cityInfos' => array ( 'name' => 'NAMPA', 'zipcodes' => array ( 0 => '18926', ), ), ), 3 => array ( 'country' => 'US', 'stateAbbrName' => 'FL', 'stateFullName' => 'Florida', 'cityInfos' => array ( 0 => array ( 'name' => 'LOWELL', 'zipcodes' => array ( 0 => '32663', ), ), ), ), 4 => array ( 'country' => 'US', 'stateAbbrName' => 'UT', 'stateFullName' => 'Utah', 'cityInfos' => array ( 0 => array ( 'name' => 'BOUNTIFUL', 'zipcodes' => array ( 0 => '84010', ), ), ), ), 5 => array ( 'country' => 'US', 'stateAbbrName' => 'SD', 'stateFullName' => 'South Dakota', 'cityInfos' => array ( 0 => array ( 'name' => 'CLAIRECITY', 'zipcodes' => array ( 0 => '57224', ), ), ), ), ), )
*/

//实例，modify()，修改记录
//'$id' => '5746e423e4017a4c09000029'，只修改这一条记录？？
		echo "Update1:<br>";
		$query = new \yii\mongodb\Query;
		$customers["population"]=80;
		$customers["name"]='Canada012323';
		$options['new']=true;//返回更新后的数据
		$new=$query->from('country')
			->modify($customers,$options);
		echo "<br>done:<br>".var_export($new,true);
/*
Update1:
done:
array ( '_id' => MongoId::__set_state(array( '$id' => '5746e423e4017a4c09000029', )), 'population' => 80, 'name' => 'Canada012323', )
*/

//create//new//createCommand
/*
createCommand()是V2.1.*才支持的命令
Class yii\mongodb\Command 
http://www.yiiframework.com/doc-2.0/yii-mongodb-command.html
*/
//创建新记录，需要"yiisoft/yii2-mongodb": "~2.1.0",
	echo "Update1:<br>";
	$query = new \yii\mongodb\Query;
	$customers["population"]=80;
	$customers["name"]='Canada012323';
	$new=\Yii::$app->mongodb->createCommand()->insert('country',$customers);
	echo "<br>done:<br>".var_export($new,true);
/*
Update1:
done:
MongoDB\BSON\ObjectID::__set_state(array())
*/

Appending Conditions
//andWhere()，追加where条件
//orWhere()
$status = 10;
$search = 'yii';
$query->where(['status' => $status]);
if (!empty($search)) {
    $query->andWhere(['like', 'title', $search]);
}
/*
WHERE (`status` = 10) AND (`title` LIKE '%yii%')
*/

//查找出name中含有ca的所有记录（不区分大小写）
	$query = Country::find();
	$query->andWhere(['like','name', 'ca']);
/*
#	Code	Name	Continent	Population	 
1	BR	Canada	Asia	170115000	  
2	C3	America	Asia	1147000	  
*/

//查询条件将被忽略
	$query = Country::find();
	$query->andWhere(['like','name','']);
/*
查询结果为所有的记录
*/

//filterWhere()，条件查询，如果字段为空则不进行查询，条件格式只能使用哈希格式（hash format）
$query->filterWhere([
    'username' => $username,
    'email' => $email,
]);

//andFilterWhere()
//orFilterWhere()
//查找出name中含有ca的所有记录（不区分大小写）
$query = Country::find();
$query->andFilterWhere(['like','name', 'ca']);
/*
#	Code	Name	Continent	Population	 
1	BR	Canada	Asia	170115000	  
2	C3	America	Asia	1147000	  
*/
//作为查询条件的写法（多字段条件查询）
D:\phpwork\basic\models\CountrySearch.php
$query->andFilterWhere(['like', 'code', $this->code])
      ->andFilterWhere(['like', 'name', $this->name]);

//andFilterCompare()
//查找出name中含有ca的所有记录（不区分大小写）
D:\phpwork\basic\models\CountrySearch.php
$query = Country::find();
$query->andFilterCompare('name', 'ca','like');
/*
#	Code	Name	Continent	Population	 
1	BR	Canada	Asia	170115000	  
2	C3	America	Asia	1147000	  
*/

//查找population>=59225700的所有记录
http://localhost:8081/web/index.php?r=country
D:\phpwork\basic\models\CountrySearch.php
$query = Country::find();
$query->andFilterCompare('population',59225700,'>=');
/*
#	Code	Name	Continent	Population	 
1	CN	China	Asia	1277558000	  
2	BR	Canada	Asia	170115000	  
*/
//错误写法，因为population为int32类型，字符串解析后仍然是字符串
//$query->andFilterCompare('population', '>=59225700');//此句无效
//$query->andFilterCompare('population', '59225700','>=');//此句无效

//orderBy()
        $query = Country::find();
        $query->orderBy([
            '_id' => SORT_ASC,//默认的排序方式
            'name' => SORT_DESC,
        ]);
//先按洲名排序，再按国家名称排序（降序）
        $query->orderBy([
            'continent' => SORT_DESC,
            'name' => SORT_DESC,
        ]);
		//与上句等效
		$query->orderBy('continent desc,name desc');
/*
#	Code	Name	Continent	Population	 
1	C0	Russia	North America	1147000	  
2	C1	Mengo	Europe	78	  
3	CA	Laowo	Europe	196	  
4	C9	France	Europe	1147000	  
5	CL	Yuenan	Asia	182	  
6	C5	TaiGuo	Asia	23424	  
7	C2	Nipor	Asia	88	  
8	C7	Montuonn	Asia	1147000	  
9	23	Korea	Asia	124234	  
10	C6	Japan	Asia	56	  
*/
//排序（升序）
        $query = Country::find();
        $query->orderBy([
            'continent' => SORT_DESC,
            'name' => SORT_ASC,
        ]);
		//与上句等效
		$query->orderBy('continent desc,name asc');

/*
#	Code	Name	Continent	Population
1	C0	Russia	North America	1147000	  
2	C9	France	Europe	1147000	  
3	CA	Laowo	Europe	196	  
4	C1	Mengo	Europe	78	  
5	C3	America	Asia	1147000	  
6	C4	Australia	Asia	1147000	  
7	BR	Canada	Asia	170115000	  
8	CN	China	Asia	1277558000	  
9	C8	England	Asia	1147000	  
10	42	Germany	Asia	234234	  
*/

//addOrderBy()，追加排序条件
        $query = Country::find();
        $query->orderBy([
            'continent' => SORT_DESC,
        ]);
        $query->addOrderBy(['name'=>SORT_ASC]);
//addOrderBy()，追加排序条件
        $query = Country::find();
        $query->orderBy('continent desc');
        $query->addOrderBy(['name'=>SORT_ASC]);

//无此方法
//groupBy()，MongoDB无此方法
//having()，MongoDB无此方法
//join()，MongoDB无此方法
//union()，MongoDB无此方法

//limit()//offset()
//查找从第3条开始的共5条记录数据（开始序号为0）
$query = new \yii\mongodb\Query;
$dataProvider = $query->from('country')->offset(3)->limit(5)->all();
/*
#	Name	Population	Continent	Code
1	Germany	234234	Asia	42
2	Canada	170115000	Asia	BR
3	Mengo	78	Europe	C1
4	Nipor	88	Asia	C2
5	America	1147000	Asia	C3
*/
//查找所有记录数据
$query = new \yii\mongodb\Query;
$dataProvider = $query->from('country')->all();
/*
#	Name	Population	Continent	Code
1	Laowo	196	Europe	CA
2	Korea	124234	Asia	23
3	China	1277558000	Asia	CN
4	Germany	234234	Asia	42
5	Canada	170115000	Asia	BR
6	Mengo	78	Europe	C1
......
*/

二、查询方法

all()
one()
column(),V2.1.2
scalar(),V2.1.2
exists()
count()
sum($q),$q:字段名
average($q)
max($q)
min($q)

源代码

//获取一条记录//one
$query = new \yii\mongodb\Query;
$result = $query->select(['addressInfos'])
	->from('accounts_frontend')
	->where(['_id' => $id])
    ->limit(1)
	->one();

//获取多条记录//all
$query = new \yii\mongodb\Query;
$result = $query->select(['responsibleRegionInfos'])
	->from('salesrep_groups')
	->where(['_id' => $id])
	->limit(10)
	->all();

//count统计记录行数
	$query = new \yii\mongodb\Query;
	$count = $query->from('country')->count();
	echo "count:".$count;
	// count:16

//column(),V2.1.2才支持
//scalar(),V2.1.2才支持

//exists()，此方法扯淡，它使用one()检索，返回的值永远不会是null（one()无值返回的是false），这种情况下exists()返回的是true!!

//sum，计算某个字段的合计值，此字段必须是int类型
	$query = new \yii\mongodb\Query;
	$count = $query->from('country')->sum('population');
	echo "sum:".$count;
	/*
	sum:1454937492
	*/
	//name是字符串类型，所以计算结果为0
	$query = new \yii\mongodb\Query;
	$count = $query->from('country')->sum('name');
	echo "sum:".$count;
	/*
	sum:0
	*/

//average($q)，计算某个字段的平均值
	$query = new \yii\mongodb\Query;
	$count = $query->from('country')->average('population');
	echo "average:".$count;
	/*
	average:90933593.25
	*/
	$count = $query->from('country')->average('name');
	/*
	average:
	*/

//max，获取一个数字字段的最大值
	$query = new \yii\mongodb\Query;
	$count = $query->from('country')->max('population');
	echo "max:".$count;
	/*
	max:1277558000
	*/

//max，获取一个数字字段的最小值
	$query = new \yii\mongodb\Query;
	$count = $query->from('country')->min('population');
	echo "min:".$count;
	/*
	min:56
	*/

//aggregate(),聚集方法,mongodb数据库中特有的方法。

//indexBy()，重新定义返回记录的键名，更改all()获取的记录键名，如果指定的字段值非唯一，则只保留最后一条记录
//indexBy()与查询方法（如groupBy()、orderBy()）不同，indexBy()是在查询结果中进行处理，所以它所定义的字段必须在select()中已定义。

文件位置：

D:\phpwork\basic\controllers\CountryController.php

源代码：

public function actionQuery() {
    $query = new \yii\mongodb\Query;
    //使用字符串字段'name'进行记录键名修改
    $dataProvider = $query->from('country')->indexBy('name')->all();
    return $this->render('query', ['dataProvider' => $dataProvider]);
}

文件位置：

D:\phpwork\basic\views\country\query.php

源代码：

<div class="country-index">
    <?php
    if($dataProvider){
        echo "<table class='table table-bordered'><tr>";
        echo "<th>#</th>";
        echo "<th>index</th>";
        echo "<th>Name</th>";
        echo "<th>Population</th>";
        echo "<th>Continent</th>";
        echo "<th>Code</th>";
        echo "</tr>";
        $i=1;
        foreach($dataProvider as $k=>$v){
            echo "<tr>";
            echo "<td>".$i."</td>";
            echo "<td>".$k."</td>";
            echo "<td>".ArrayHelper::getValue($v,'name')."</td>";
            echo "<td>".ArrayHelper::getValue($v,'population')."</td>";
            echo "<td>".ArrayHelper::getValue($v,'continent')."</td>";
            echo "<td>".ArrayHelper::getValue($v,'code')."</td>";
            echo "</tr>";
            $i++;
        }
        echo "</table>";
    }else{
        echo "no record";
    }
    ?>
</div>

测试结果

<?
/*
#	index	Name	Population	Continent	Code
1	Laowo	Laowo	196	Europe	CA
2	Korea	Korea	124234	Asia	23
3	China	China	1277558000	Asia	CN
4	Germany	Germany	234234	Asia	42
5	Canada	Canada	170115000	Asia	BR
6	Mengo	Mengo	78	Europe	C1
7	Nipor	Nipor	88	Asia	C2
8	America	America	1147000	Asia	C3
9	Australia	Australia	1147000	Asia	C4
10	TaiGuo	TaiGuo	23424	Asia	C5
11	Japan	Japan	56	Asia	C6
12	Montuonn	Montuonn	1147000	Asia	C7
13	England	England	1147000	Asia	C8
14	France	France	1147000	Europe	C9
15	Russia	Russia	1147000	North America	C0
16	Yuenan	Yuenan	182	Asia	CL
*/
//重复键名将被替代掉，population字段有重复值，则population相同的记录只保留最后一条，16条记录被合并成了11条
	$query = new \yii\mongodb\Query;
	$dataProvider = $query->from('country')->indexBy('population')->all();
/*
#	index	Name	Population	Continent	Code
1	196	Laowo	196	Europe	CA
2	124234	Korea	124234	Asia	23
3	1277558000	China	1277558000	Asia	CN
4	234234	Germany	234234	Asia	42
5	170115000	Canada	170115000	Asia	BR
6	78	Mengo	78	Europe	C1
7	88	Nipor	88	Asia	C2
8	1147000	Russia	1147000	North America	C0
9	23424	TaiGuo	23424	Asia	C5
10	56	Japan	56	Asia	C6
11	182	Yuenan	182	Asia	CL
*/
//使用匿名函数重新定义键名
	$query = new \yii\mongodb\Query;
	$dataProvider = $query->from('country')->indexBy(function($row){
		return $row['name'].$row['population'];
	})->all();
/*
#	index	Name	Population	Continent	Code
1	Laowo196	Laowo	196	Europe	CA
2	Korea124234	Korea	124234	Asia	23
3	China1277558000	China	1277558000	Asia	CN
4	Germany234234	Germany	234234	Asia	42
5	Canada170115000	Canada	170115000	Asia	BR
6	Mengo78	Mengo	78	Europe	C1
7	Nipor88	Nipor	88	Asia	C2
8	America1147000	America	1147000	Asia	C3
9	Australia1147000	Australia	1147000	Asia	C4
10	TaiGuo23424	TaiGuo	23424	Asia	C5
11	Japan56	Japan	56	Asia	C6
12	Montuonn1147000	Montuonn	1147000	Asia	C7
13	England1147000	England	1147000	Asia	C8
14	France1147000	France	1147000	Europe	C9
15	Russia1147000	Russia	1147000	North America	C0
16	Yuenan182	Yuenan	182	Asia	CL
*/

//Batch Query，批量查询，以解决大量数据被读取消耗过多内存的问题，批量查询使用数据游标（data cursor）
//批量查询的方法在V2.1才有效
$query->batch()
$query->each()

http://www.yiiframework.com/doc-2.0/yii-mongodb-querybuilder.html

//QueryBuilder//Query Builder yii\mongodb\QueryBuilder,在V2.1中才有效 //V2.1//V2.0//version V2.1是指MongoDb Extension for Yii 2 V2.1. V2.0是指MongoDb Extension for Yii 2 V2.0. // QueryBuilder执行查询操作的步骤：（仅适用于MySQL，非MongoDB步骤）

根据yii\db\Query的结构调用yii\db\QueryBuiler创建一个SQL语句
使用上步中生成的SQL语句创建一个yii\db\Command对象
调用yii\db\Command的一个查询方法（如queryAll()）执行SQL语句，并获取数据。 //生成SQL语句（仅适用于MySql，非MongoDB）有时你可能会需要检查或使用SQL语句，而这个SQL语句是使用yii\db\Query对象创建的。

源代码：

$command = (new \yii\db\Query())
    ->select(['id', 'email'])
    ->from('user')
    ->where(['last_name' => 'Smith'])
    ->limit(10)
    ->createCommand();    
// show the SQL statement
echo $command->sql;
// show the parameters to be bound
print_r($command->params);
// returns all rows of the query result
$rows = $command->queryAll();

（全文完）

没有找到数据。

您需要登录后才可以回复。登录 | 立即注册

副董事长

阿江

注册时间：2015-10-18
最后登录：2024-03-03
在线时长：186小时21分

粉丝94
金钱16816
威望160
积分20276

[Yii2笔记]004 Yii2基于MongoDB构建查询和调用查询方法 [ 技术分享 ]

说明

原文网址：

本文主题：Yii2基于MongoDB构建查询和调用查询方法

一、构建查询

连贯操作Yii2源码文件的位置：

查询演示的一个完整例子

文件位置：

源代码：

文件位置：

源代码：

源代码：

二、查询方法

源代码

文件位置：

源代码：

文件位置：

源代码：

测试结果

源代码：

（全文完）

共 0 条回复

回复话题

阿江

热门话题