Using SQL 'AS' statement in Yii CDBCriteri

问题:

Could anyone tell me how to use an sql 'AS' statement in a CDBCriteria query?

Whatever i have tired just brings back the columns names of my table in an array and no sign of my sql alias.

I think i may have to add it into my model class but im not sure where to declare it after several attempts to add it in.

Here is mine, as you can see 'distance' is my alias:

    $criteria= new CDbCriteria;
    $criteria->select='*, 3963 * acos(cos(radians('.$distanceString['latitude'].')) * cos(radians(latitude)) * cos(radians('.$distanceString['longitude'].') - radians(longitude)) + sin(radians('.$distanceString['latitude'].')) * sin(radians(latitude))) AS distance';
    $criteria->condition=$sqlCondition;
    $criteria->params=$sqlVariables;
    $criteria->order='distance';

Thanks for any tips or help in advance! :)

回答1:

Do you use this with a CActiveDataProvider for an active record class? If so, try defining distance in that class.

回答2:

Use CDbCommand instead

$rawData=Yii::app()->db->createCommand()
    ->select('*, 3963 * acos(cos(radians('.$distanceString['latitude'].')) * cos(radians(latitude)) * cos(radians('.$distanceString['longitude'].') - radians(longitude)) + sin(radians('.$distanceString['latitude'].')) * sin(radians(latitude))) AS distance')
    ->from('TableName')
    ->where($sqlCondition)
    ->order('distance')->queryAll(true,$sqlVariables);

eg. together with CArrayDataProvider

$dataProvider=new CArrayDataProvider($rawData);
// $dataProvider->getData() will return a list of arrays.