$pages->applyLimit导致的查询问题

International Chinese
1

在qiang的关心下已经解决，原来是我自己不会用，参见qiang的回答

一旦应用，会导致查询得到的结果




		$pid = Yii::app()->request->getParam('pid');

		

		$criteria = new CDbCriteria;

		$criteria->with = array('disinfectassign'=>array('condition'=>'pigid='.$pid));


		

		$pages=new CPagination(disinfect::model()->count($criteria));

		$pages->pageSize=self::PAGE_SIZE;

//		$pages->applyLimit($criteria);


		$sort=new CSort('disinfect');

		$sort->applyOrder($criteria);


		$models=disinfect::model()->findAll($criteria);		


		$this->render('list',array(

			'models'=>$models,

			'pages'=>$pages,

			'sort'=>$sort,

		));
2

假设正确结果是4条

一旦取消注释部分内容，查询是的前面30条

3

经过跟踪发现 where 没有了，联合查询也没有

貌似没有引起findAll的with，而count却没有问题with起总用了

这是count的sql

SELECT COUNT(DISTINCT t.id) FROM disinfect t INNER JOIN disinfectassign disinfectassign ON (disinfectassign.disinfectid=t.id) WHERE (pigid=621)"

这是findAll的sql语句

SELECT t.id AS t0_c0 , t.name AS t0_c1 , t.medid AS t0_c2 , t.date AS t0_c3 , t.jiliang AS t0_c4 , t.count AS t0_c5 , t.beizhu AS t0_c6

FROM disinfect t

LIMIT 15

这是注释$pages->applyLimit($criteria);后的findAll的SQL语句

SELECT t.id AS t0_c0, t.name AS t0_c1, t.medid AS t0_c2, t.date AS t0_c3, t.jiliang AS t0_c4, t.count AS t0_c5, t.beizhu AS t0_c6, disinfectassign.id AS t1_c0, disinfectassign.disinfectid AS t1_c1, disinfectassign.pigid AS t1_c2 FROM disinfect t INNER JOIN disinfectassign disinfectassign ON (disinfectassign.disinfectid=t.id) WHERE (pigid=621)"

4

呼唤强总

5

disinfectassign是什么关系？HAS_ONE/BELONGS_TO还是 HAS_MANY？

6

model/disinfect.php




	/**

	 * @return array relational rules.

	 */

	public function relations()

	{

		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

			'med'=>array(self::BELONGS_TO, 'Medicines', 'medid'),

			'disinfectassign'=>array(self::HAS_MANY, 'disinfectassign', 'disinfectid', 'joinType'=>'INNER JOIN'),

		);

	}

model/disinfectionassign.php




	/**

	 * @return array relational rules.

	 */

	public function relations()

	{

		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

			'disinfect'=>array(self::BELONGS_TO, 'disinfect', 'disinfectid'),

		);

	}
7

是HAS_MANY的关系。因为有limit，这种一对多的情况如果一起join，是做不出正确的limit的。所以yii缺省是分成两个SQL来做。你可以用with()->together()来强制用一个SQL进行查询。

8

感谢，原来是我个人不了解，感谢