SQL_CALC_FOUND_ROWS

Yii 1.1 General Discussion
1

Hi!

Anybody know how to treat with SQL_CALC_FOUND_ROWS in yii? I use query builder and wanna calc the founded rows somehow. The query is big and complex, so using COUNT(*) is not recommended.

Such method does not work:


$model = $connection->createCommand()

	->select('SQL_CALC_FOUND_ROWS field1, field2')

	->from('file')

	->queryRow();


$count = $connection->createCommand()

	->select('FOUND_ROWS() AS qnt')

	->from('file')

	->queryRow();

The zero returned in qnt field.

2

Hi,

did you found a solution?

I am quite interessted in this too.

Greetings Jan

3

Use this:


$count = $connection->createCommand('SELECT FOUND_ROWS()')->queryScalar();
4

HI,

It is not working. Would appreciate if someone can give more guide here. Thanks

5

It worked actually. I did it via createCommand()





$command = Yii::app()->db->createCommand()

				    ->select('SQL_CALC_FOUND_ROWS f.id, f.name, f.full_address, f.status,ft.type_id, COUNT(r.id) as reviewsCount, COUNT(i.id) as imagesCount')

				    ->from('tbl_facility f')

				    ->join('tbl_facility_type ft', 'ft.facility_id=f.id')

				    ->leftjoin('tbl_review r', 'r.facility_id=f.id AND r.status!=\'H\'')

				    ->leftjoin('tbl_image i', 'i.facility_id=f.id AND i.status=\'P\'')

				    ->where($query_condition)

				    ->group('f.id')

				    ->limit($pageLimit, $offset);


			$command->setFetchMode(PDO::FETCH_OBJ);//fetch each row as Object

			$results=$command->queryAll();

			$count = Yii::app()->db->createCommand('SELECT FOUND_ROWS()')->queryScalar();
6

For me, the code


select('SQL_CALC_FOUND_ROWS f.id, f.name, f.full_address ...')

is being interpreted as


SELECT SQL_CALC_FOUND_ROWS AS f.id, f.name ...

Any ideas how to overcome this.

I tried


select('SQL_CALC_FOUND_ROWS (0), f.id, f.name, f.full_address ...')

to no avail.