[manu]

Hi,

I have a trouble with AR and this query (Using findAllBySql function)

$query = "SELECT date, GROUP_CONCAT(position ORDER BY engine SEPARATOR ' ') as positions FROM TResults WHERE idSearch=1 GROUP BY date;";
$results=Results::model()->findAllBySql($query);
foreach ($results as $res)
{
echo "Date = $res->date AND positions = $res->positions";
}

Yii launch me the following error:

Internal Server Error
Property "Results.positions" is not defined.

I think the query is OK because I have done a little example using "mysql_" functions and it works perfectly. Maybe there are some typographic errors in this message but aren't the problem.

This is my table TResults

idResult  int(11)
idSearch int(11)
date date
position int(11)
engine varchar(45)

The intention is to get something like this:

date              positions
2009-05-08  10 20 30
2009-05-09  70 80 90

from this recordset
idResult idSearch  date            position engine
1          1              2009-05-08  10          'A'
2          1              2009-05-08  20          'B'
3          1              2009-05-08  30          'C'
4          1              2009-05-09  70          'A'
5          1              2009-05-09  80          'B'
6          1              2009-05-09  90          'C'

Thanks
Manu

[Mike]

Add a public variable $positions to your model.

[manu]

Oh! Ok, It work's fine.

Thanks Mike
Manu

[mtaki]

manu, on 11 May 2009 - 03:52 AM, said:

Oh! Ok, It work's fine.

Thanks Mike
Manu

I was experiencing the same error in using GROUP_CONCAT() ,but after reading your post,i finally solve my problem.
but when i use comma separator i get error
my code:$criteria->select="t.id,GROUP_CONCAT(licenceApplications.id SEPARATOR ',') as licence";
error:Active record "Operator" is trying to select an invalid column "GROUP_CONCAT(licenceApplications.id SEPARATOR '". Note, the column must exist in the table or be an expression with alias.

[Mike]

mtaki, on 19 September 2012 - 04:45 AM, said:

but when i use comma separator i get error
my code:$criteria->select="t.id,GROUP_CONCAT(licenceApplications.id SEPARATOR ',') as licence";

Try an array instead:

$criteria->select = array('t.id', "GROUP_CONCAT(licenceApplications.id SEPARATOR ',') as licence");