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?