Yii Framework Forum: AR and GROUP_CONCAT query - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • This topic is locked

AR and GROUP_CONCAT query Rate Topic: -----

#1 User is offline   manu 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 9
  • Joined: 27-April 09

Posted 11 May 2009 - 03:28 AM

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

0

#2 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 11 May 2009 - 03:42 AM

Add a public variable $positions to your model.
0

#3 User is offline   manu 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 9
  • Joined: 27-April 09

Posted 11 May 2009 - 03:52 AM

Oh! Ok, It work's fine.

Thanks Mike
Manu
0

#4 User is offline   mtaki 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 14
  • Joined: 20-July 11
  • Location:Tanzania

Posted 19 September 2012 - 04:45 AM

View Postmanu, 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.
0

#5 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 20 September 2012 - 01:18 AM

View Postmtaki, 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");


0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • This topic is locked

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users