iGrog
(Laritari)
January 6, 2010, 10:39am
1
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
(Live Webscore)
January 6, 2010, 10:46am
2
Try $criteria->select = array(‘tableName.*’, ‘SUM(Rate) AS rating’);
bettor
(Live Webscore)
January 6, 2010, 1:41pm
4
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
(Laritari)
January 6, 2010, 2:25pm
5
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.
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.
maybe using Users::model()->getMetaData()->columns ?
$criteria->select = array_merge(Users::model()->getMetaData()->columns, array('SUM(Rate) AS rating'));
bettor
(Live Webscore)
January 6, 2010, 4:19pm
7
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.
$criteria->select = "Users.*, SUM(Rate) AS rating"; //capitalize users if your table starts with upper case letter
bettor
(Live Webscore)
January 7, 2010, 12:05pm
8
…also check your $model = Users::model()->findAll($criteria);
iGrog
(Laritari)
January 8, 2010, 7:51am
9
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
iGrog
(Laritari)
January 8, 2010, 7:52am
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);