Yii Framework Forum: Using sum in CDbCriteria - Yii Framework Forum

Jump to content

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

Using sum in CDbCriteria Rate Topic: ****- 1 Votes

#1 User is offline   ryland22 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 36
  • Joined: 05-March 11

Posted 25 January 2012 - 02:51 PM

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.
0

#2 User is offline   ORey 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,703
  • Joined: 20-April 09
  • Location:Moscow, Russia

Posted 25 January 2012 - 02:59 PM

Try this:

$criteria->select = 'sum(views) AS viewsCount, userName';

God is real unless declared as integer
0

#3 User is offline   ryland22 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 36
  • Joined: 05-March 11

Posted 25 January 2012 - 03:13 PM

Yep - that worked, just needed to add an alias.
0

#4 User is offline   FJPC 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 16-December 12

Posted 16 December 2012 - 06:53 PM

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
0

#5 User is offline   francis ja 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 31
  • Joined: 27-July 11
  • Location:India

Posted 08 March 2013 - 02:36 PM

how i can find user who having views more than 5 using CDbCriteria
ie how to use sum condition in CDbCriteria?
skype: jesusloves.francis
Blogs:
http://yiideveloper.wordpress.com/
http://francis-yii-developer.me
Co-founder of Nintriva Wireless
mails:
jesusloves.francis@gmail.com
francis@nintriva.com
http://nintriva.com
0

#6 User is offline   francis ja 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 31
  • Joined: 27-July 11
  • Location:India

Posted 27 March 2013 - 02:16 AM

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;

skype: jesusloves.francis
Blogs:
http://yiideveloper.wordpress.com/
http://francis-yii-developer.me
Co-founder of Nintriva Wireless
mails:
jesusloves.francis@gmail.com
francis@nintriva.com
http://nintriva.com
0

#7 User is offline   qingyue 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 29
  • Joined: 18-September 09

Posted 24 December 2013 - 02:41 AM

View PostFJPC, on 16 December 2012 - 06:53 PM, said:

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



add a property name with viewsCount in model file, such as:
public viewsCount;
0

#8 User is offline   paddysun2211 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 01-August 14

Posted 08 August 2014 - 03:03 AM

View Postfrancis ja, on 08 March 2013 - 02:36 PM, said:

how i can find user who having views more than 5 using CDbCriteria
ie how to use sum condition in CDbCriteria?bos tay

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