How to select with grouping?

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?

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

Same error

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.

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')); 



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

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

Same error :)

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



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);