Yii Framework Forum: How to select with grouping? - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

How to select with grouping? Rate Topic: -----

#1 User is offline   iGrog 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 64
  • Joined: 09-October 09

Posted 06 January 2010 - 05:39 AM

I have 3 tables:
Users (UID [PK])
Goods (GID [PK])
Ratings (UID, GID, Rate)

I'd like to exec this SQL but as ActiveRecord, to get most rated goods with related info:

SELECT *, SUM(Rate) AS Rating
FROM Ratings AS r, Users AS u, Goods AS g
WHERE r.GID=g.GID AND u.UID=g.UID
GROUP BY r.GID
ORDER BY Rating DESC
LIMIT 10


I'm trying to write CDbCriteria:

        $criteria = new CDbCriteria();
        $criteria->limit = $limit;
        $criteria->order = "Rating DESC";
        $criteria->select = array('*', 'SUM(Rate) AS rating');
        $criteria->group = "rating.GID";

        $mostRelated = Goods::model()->with("rating", "users")->together()->findAll($criteria);


But I get error: '*' is wrong column
What's wrong?
0

#2 User is offline   bettor 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 751
  • Joined: 02-February 09

Posted 06 January 2010 - 05:46 AM

Try $criteria->select = array('tableName.*', 'SUM(Rate) AS rating');
0

#3 User is offline   iGrog 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 64
  • Joined: 09-October 09

Posted 06 January 2010 - 06:23 AM

Same error
0

#4 User is offline   bettor 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 751
  • Joined: 02-February 09

Posted 06 January 2010 - 08:41 AM

Did you try using a string instead of array:
$criteria->select = "tableName.*, SUM(Rate) AS rating";

Thats how I have it in my app and is working fine.
0

#5 User is offline   iGrog 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 64
  • Joined: 09-October 09

Posted 06 January 2010 - 09:25 AM

Yes, I tried any variations, but it doesn't work! :)
The exact error is:
$criteria->select = "users.*, SUM(Rate) AS rating"; 

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

0

#6 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 06 January 2010 - 09:44 AM

View PostiGrog, on 06 January 2010 - 09:25 AM, said:

Yes, I tried any variations, but it doesn't work! :)
The exact error is:
$criteria->select = "users.*, SUM(Rate) AS rating"; 

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



maybe using Users::model()->getMetaData()->columns ?

    $criteria->select = array_merge(Users::model()->getMetaData()->columns, array('SUM(Rate) AS rating')); 

KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#7 User is offline   bettor 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 751
  • Joined: 02-February 09

Posted 06 January 2010 - 11:19 AM

View PostiGrog, on 06 January 2010 - 09:25 AM, said:

Yes, I tried any variations, but it doesn't work! :)
The exact error is:
$criteria->select = "users.*, SUM(Rate) AS rating"; 

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



$criteria->select = "Users.*, SUM(Rate) AS rating"; //capitalize users if your table starts with upper case letter
0

#8 User is offline   bettor 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 751
  • Joined: 02-February 09

Posted 07 January 2010 - 07:05 AM

...also check your $model = Users::model()->findAll($criteria);
0

#9 User is offline   iGrog 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 64
  • Joined: 09-October 09

Posted 08 January 2010 - 02:51 AM

View Postbettor, on 06 January 2010 - 11:19 AM, said:

$criteria->select = "Users.*, SUM(Rate) AS rating"; //capitalize users if your table starts with upper case letter


Same error :)

Quote

...also check your $model = Users::model()->findAll($criteria);


What is this? Why I need to check Users::model()->findAll($criteria)?
I need to group and order rating table. Once again, I need to convert this SQL to AR statements:
SELECT *, r.GID, SUM(Rate) AS Rating
FROM Ratings AS r, Users AS u, Goods AS g
WHERE r.GID=g.GID AND u.UID=g.UID
GROUP BY r.GID
ORDER BY Rating DESC
LIMIT 10

0

#10 User is offline   iGrog 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 64
  • Joined: 09-October 09

Posted 08 January 2010 - 02:52 AM

He he, simply removing '*' from $criteria->select seems to work:
        $criteria = new CDbCriteria();
        $criteria->limit = 10;
        $criteria->order = "rating DESC";
        $criteria->group = "ratings.PID";
        $criteria->select = array('SUM(Rate) AS rating'); 
        $goods = Goods::model()->with("ratings", "users")->together()->findAll($criteria);

0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

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