Using "as" in CDbCriteria->select

Archives Yii 1.0.x Discussions
1

Hi there,

I'm trying to sort a list of rows by distance using the CDbCriteria object, here's the code i have so far:

$criteria->select = "*,((ACOS(".sin($lo_place->latitude * pi() / 180)." * SIN(`latitude` * ".pi()." / 180) + ".COS($lo_place->latitude * PI() / 180)." * COS(`latitude` * ".PI()." / 180) * COS(($lo_place->longitude - `longitude`) * ".PI()." / 180)) * 180 / ".PI().") * 60 * 1.1515) AS `distance`";


$criteria->order = "distance ASC";


$criteria->limit = 10;

but i get the following error: Column not found: 1054 Unknown column 'distance' in 'order clause'

Is this a limitation of active record or am I doing something wrong? will i have to resort to CDbCommand ?

Many thanks for your help.

2

It's SQL error. You are using column alias in the ORDER part, which is not allowed by SQL.

3

Actually it is allow qiang. The following is an example that certainly works:

select column as col from table order by col
4

Yeah, I'm actually porting this statement which defintiely works:

$sql = "SELECT $ps_columns,((ACOS(".sin($this->ud_latitude * pi() / 180)." * SIN(`latitude` * ".pi()." / 180) + ".COS($this->ud_latitude * PI() / 180)." * COS(`latitude` * ".PI()." / 180) * COS(($this->ud_longitude - `longitude`) * ".PI()." / 180)) * 180 / ".PI().") * 60 * 1.1515) AS `distance` FROM `business` WHERE `id` IN (".implode(",",$this->ua_inlist).") ORDER BY `distance` ASC LIMIT $li_start,$this->ui_page_items";

So I don't think it's an SQL thing.

5

My bad, what is the generated SQL?

6

Hmm is there an easy way to output the sql generated by active record? I can't find anything in the docs.

Thanks

7

You may turn on logging (without any log filters) to see SQLs.

8

It's producing:

SELECT COUNT(*) FROM venue ORDER BY distance ASC

Then it errors.

9

I guess that's because you are calling count() method with the order clause. Do not set the order clause with count().

10

Yeah, i've got it working now, I had to move the $criteria->order to a later point in the code, after i'd called the pagination widget.

Many thanks for your help.

11

Hello again,

The problem is now I can't access the distance value from the model that gets returned. It looks like CActiveRecord doesn't notice the additional columns that have been selected. I was thinking I could just use CDbCommand to return the ids and the distance of the rows, then load the models afterwards but then i'm doubling the number of queries…

12

Think of Views if your DBMS permits Views.