Getting the rcord index using CDBCriteria

Yii 1.1 General Discussion
1

hi all pls tell me how to get the rcord index with results return by CDBCriteria.

I want to do something like this:

SET @rank=0;

SELECT @rank:=@rank+1 AS rank, fruit, amount

FROM sales

ORDER BY amount DESC

;

I tried:

$criteria = new CDbCriteria;

$criteria->params = "SET @rownum := 0,";

$criteria->select = ‘@rownum := @rownum + 1 AS rank,t.id,t.business_name,bc.category_name’;

$criteria->join .= ’ INNER JOIN business_categories bc ON t.business_category_id = bc.id’;

but rank is always empty.

Also tried with : $criteria->index=‘rank’;

But its always empty.

Any suggestions?

2

I suspect the alias. Did you try to use a regular column as index? And did you also define a public property ‘rank’ in your model?

Apart from that: Even if it worked, your code seem to return a 0 based index anyway. Or am i missing something? Never worked with variables in MySQL.

3

@Mike

Thnx for the quick attention.

The generic SQL I posted is working properly.

I tried using regular column as index but no luck : $criteria->index=‘id’;

Yes I defined a public property ‘rank’ in my model.

What I need is record ranking to use with ‘alphapager’ extension:

http://www.yiiframework.com/forum/index.php?/topic/8992-extension-alphapager/page__view__findpost__p__121128

But no need to worry about the extension if there’s any possible way of getting the record ranking with CDbCriteria.