I need to sum a column as I pull it out of a database. My sql query works fine and is as follows:
SELECT User.userName, sum(views) FROM Rant LEFT JOIN User ON Rant.userId = User.userId GROUP BY User.userId ORDER BY views DESC LIMIT 5;
My CDbCriteria is as follows:
$criteria=new CDbCriteria;
$criteria->select = 'sum(views), userName';
$criteria->with = 'user';
$criteria->order='views DESC';
$criteria->group = 'user.userId';
$criteria->limit=5;
$dataProvider3=new CActiveDataProvider('Rant', array(
'criteria'=>$criteria,
));
I’m getting the following error when I try to loop through the data in my view:
record "Rant" is trying to select an invalid column "sum(views)". Note, the column must exist in the table or be an expression with alias.
Try this:
$criteria->select = 'sum(views) AS viewsCount, userName';
Yep - that worked, just needed to add an alias.
siskolnick
(Fcojpc89)
4
How do you display the column "viewsCount" in a CGridView?
I tried with my own column with an alias “total” like $data->total , but it didn’t work
22francis
(Jesusloves Francis)
5
how i can find user who having views more than 5 using CDbCriteria
ie how to use sum condition in CDbCriteria?
22francis
(Jesusloves Francis)
6
i got a solution for it
$detailTable = Payments::model()->tableName();
$detailTableSql = "payments.amount IS NULL OR (SELECT SUM(amount) FROM $detailTable WHERE $detailTable.order_id = t.id)<t.total";
$criteria=new CDbCriteria;
$criteria->condition = $detailTableSql;
qingyue
(Cn2156)
7
add a property name with viewsCount in model file, such as:
public viewsCount;