Ar Fails With Criteria Having Select Contain Database Functions And With Property

Yii 1.1 General Discussion
1

I create a CDbCriteria object that is going to be used to find records through AR.

In the select property of the criteria object, I have database functions, like SUBSTR or CONCAT. The problem is when I try to get the related records with the with property. If I don’t get the child records, the query runs ok, but if I uncomment the with property, I get an exception.

Code:




$criteria = new CDbCriteria;

$criteria->select = 't.name, CONCAT(t.street_address, \' \', t.street_address2) AS address, '

  . 't.phone_ext1, t.phone_ext2, t.description';

$criteria->condition = 't.active=1';

$criteria->with = array('idState', 'idCity');

$criteria->order = 'idState.name, idCity.name, t.name';

If I comment the with and order properties (because the latter depends on the former), the query runs fine.

The exception I get is:


2012/10/24 22:55:57 [error] [exception.CDbException] exception 'CDbException' with message 'Active record "CustomerLocations" is trying to select an invalid column "' '". Note, the column must exist in the table or be an expression with alias.' in .../source/yii/framework/db/ar/CActiveFinder.php:940

Stack trace:

#0 /.../source/yii/framework/db/ar/CActiveFinder.php(1232): CJoinElement->getColumnSelect('t.name, CONCAT(...')

Is there something I’m forgetting here?

2

Do you get the same error if you use double quotes to delineate the space character instead of escaped single quotes?

3

change it


$criteria = new CDbCriteria;

$criteria->select = 't.name, CONCAT(t.street_address, \' \', t.street_address2) AS address, '

  . 't.phone_ext1, t.phone_ext2, t.description';

$criteria->condition = 't.active=1';

$criteria->with = array('idState', 'idCity');

$criteria->order = 'idState.name, idCity.name, t.name';

to


$criteria = new CDbCriteria;

$criteria->select = "t.name, CONCAT(t.street_address, ' ', t.street_address2) AS address, t.phone_ext1, t.phone_ext2, t.description";

$criteria->condition = 't.active=1';

$criteria->with = array('idState', 'idCity');

$criteria->order = 'idState.name, idCity.name, t.name';

NOTE:

  1. dont forget to define a address property in your model in order to do something like $model->address;
4

Same error.

I changed it to


$criteria->select = "t.name, CONCAT(t.street_address, ' ', t.street_address2) AS address, t.phone_ext1, t.phone_ext2, t.description";

and it still fails as I said above. However, if I omit the ’ ’ string, it works. Yes, I didn’t forget to assign a address property in my model.

I suspect this is some kind of a bug.

5

may be you can try and concatenate in mysql cli and then try in your yii app because i tested the same code i worked for me… weird

6

If you mean to try and run the query in mysql client, yes, I already did (I usually first run the queries there and then write them in PHP) and it had no issues.

Anyway, this is a rather simple case. Instead of concatenating the fields in MySQL, I do this in PHP, so no harm done. However, there could be cases that I couldn’t circumvent this issue in PHP, because I could use MySQL functions that do not exist in PHP.

Could a Yii developer shed some light here?

7

Goofyx if you wanna use yii there is afterFind() hook on AR you can write something like following

// in your model

public $address=’’;


public function afterFind()

{

   $this->street_address. " ". $this->.street_address2;

}

it will do the exact same thing

8

Ok, thanks for the tip. But this is an event that runs every each find, which is not always the case for me (plus the small overhead in each find()). And it’s inside the PHP code. As I said, what about other MySQL functions that I would like to run that couldn’t be “emulated” in PHP?

9

Goofyx mysql has a huge list of function you probably need to look at the mysql docs, I dont there any mysql function that can not be emulated by php, not one that i know