[iGrog]

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?

[bettor]

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

[iGrog]

Same error

[bettor]

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.

[iGrog]

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.

[gallego123]

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

[bettor]

iGrog, 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

[bettor]

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

[iGrog]

bettor, 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

[iGrog]

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