SQL SUBSTRING in CDbCriteria

Yii 1.1 General Discussion
1

That code works fine.


$cr = new CDbCriteria();

$cr->select = "SUBSTRING(movie_title,1,1) as letter";

$movies = Movie::model()->findAll($cr);

But i get error, if i add relations to query


$cr = new CDbCriteria();

$cr->select = "SUBSTRING(movie_title,1,1) as letter";

$movies = Movie::model()->with('types')->findAll($cr);

Active record "Movie" is trying to select an invalid column "1". Note, the column must exist in the table or be an expression with alias.

I tried to add $letter property to Movie model, but nothing changed.

Also i tried


$cr = new CDbCriteria();

$cr->select = "SUBSTRING(movie_title,1) as letter";

$movies = Movie::model()->with('types')->findAll($cr);

and got following error

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘) as letter, t.movie_id AS t0_c0, types.type_id AS t1_c0, types.`t’ at line 1

Log shows next query, and query doesn’t contain SUBSTRING !


 SELECT `t`.`movie_title` AS `t0_c1`, 1) as letter,

`t`.`movie_id` AS `t0_c0`, `types`.`type_id` AS `t1_c0`, `types`.`title` AS

`t1_c1`, `types`.`general_title` AS `t1_c2`, `types`.`price` AS `t1_c3`,

`types`.`image` AS `t1_c4`, `types`.`weight` AS `t1_c5`, `types`.`show` AS

`t1_c6` FROM `Movie` `t`  LEFT OUTER JOIN `File` `types_types` ON

(`t`.`movie_id`=`types_types`.`movie_id`) LEFT OUTER JOIN `Type` `types` ON

(`types`.`type_id`=`types_types`.`type_id`) ORDER BY types.weight DESC

Any ideas how to fix it?

2

Try:


$cr->select = "SUBSTRING(t.movie_title,1,1) as letter";
3

I wtote in my post above, that i tried that case.

4

IIRC you can use an array for the select value, somethink like this




array('SUBSTRING(movie_title,1,1) as letter')

/Tommy

5

Where? You did notice the t. in front of movie_title, did you?

$cr->select = "SUBSTRING(t.movie_title,1,1) as letter";

6

Sorry Mike, didn’t i notice that. But i tried that case, and it didn’t help.

Thanks, Tommy. Array in select helps!

7

I had this problem too, luckily I found this problem.

Any ideas why we can’t use SUBSTRING function when specifying the ‘select’ clause as a string? The documentation doesn’t mention that we need to use array format for this to work.

8

It’s needed for SQL functions that takes more than one parameter. I agree, a few words in the documentation wouldn’t hurt.

/Tommy