AR and GROUP_CONCAT query

Archives Yii 1.0.x Discussions
1

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 &quot;Date = $res-&gt;date AND positions = <strong class='bbc'>$res-&gt;positions</strong>&quot;;

}

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

2

Add a public variable $positions to your model.

3

Oh! Ok, It work's fine.

Thanks Mike

Manu

4

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.

5

Try an array instead:


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