Yii Framework Forum: CDBCriteria to CDbCommand - Yii Framework Forum

Jump to content

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

CDBCriteria to CDbCommand Rate Topic: -----

#1 User is offline   twocandles 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 58
  • Joined: 23-December 10

Posted 28 February 2011 - 02:22 PM

Hi,

I have a query with somewhat tricky conditions. I've built a CDbCriteria to fill CActiveProvider that feeds a CGridView. So far so good, but I need to make some aggregate calculations on the same criteria conditions (SUMs and so on) and I can't use a CActiveRecord for that: i tried selecting a SUM(...) but it doesn't work.

Is there a way to reuse the criteria to feed a CDbCommand? I was thinking of extracting criteria conditions and supplying them to the command, but maybe there's a better approach.

Thank you.
0

#2 User is offline   Gustavo 

  • Master Member
  • Yii
  • Group: Moderators
  • Posts: 916
  • Joined: 27-July 10
  • Location:Curitiba - Brasil

Posted 28 February 2011 - 02:26 PM

for sum() to work you must use CDbExpression
also you can build an active data provider from pure sql

I'm not very experinced in it, someone might answer you better, but that is the way I would do it
--
Extensions:
translate modue - module to handle translations
multiActiveRecord - db selection in models
redisCache - redis cache component
mpCpanel - interact with cpanel api
mUploadify - use uploadify uploader in your application

Gustavo Salomé Silva
0

#3 User is offline   Yoda Pop 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 54
  • Joined: 11-August 10
  • Location:Ontario, Ca

Posted 28 February 2011 - 04:18 PM

There is a topic you can read through that might help you here.
0

#4 User is offline   twocandles 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 58
  • Joined: 23-December 10

Posted 01 March 2011 - 01:54 AM

Thanks to both, but I think I didn't explain myself.

I have a CDbCriteria to which I've added some conditions. I can use that CDbCriteria with a CActiveProvider/CActiveRecord, but if I want to reuse the same conditions for an aggregate query I dont know how. I wouldn't want to rewrite the same "where" conditions with the CDbCommand API...
0

#5 User is offline   Yoda Pop 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 54
  • Joined: 11-August 10
  • Location:Ontario, Ca

Posted 01 March 2011 - 05:04 PM

The link I posted above does give you some information on how to use an aggregate function within CDbCriteria properties, it's just in the context of a relation. You'll want to put the function in your select string/array with an alias
$criteria->select = 'SUM(columnA) as aSum, columnB'

then you need to make sure that you use group by on a column other than the one you are performing the aggregate function on
$criteria->group = 'columnB'

Sometimes it won't work unless you add the alias as a property in your AR model. Following my example, you would add the public property $aSum to the AR model you are using with CActiveDataProvider. If the aggregate function needs to be applied to a model relation, you can see how to do that in the link I posted above.
0

#6 User is offline   twocandles 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 58
  • Joined: 23-December 10

Posted 02 March 2011 - 02:00 AM

OK, now I understand...

The trick is to add the property to the model, thank you very much!!
0

#7 User is offline   twocandles 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 58
  • Joined: 23-December 10

Posted 03 March 2011 - 02:39 PM

Well, it works...partially.

If a "simple" expresssion is used, like
sum( field1 ) as my_sum

works fine, but my expression looks like this:

sum( hour( timediff( date_end, date_ini ) ) ) as total_hours


and that gives me a "ActiveRecord is trying to select an invalid column" ...
0

#8 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 04 March 2011 - 05:00 AM

Try to specify the select columns in your CDbCriteria as Array instead of a comma separated list.
0

#9 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 04 March 2011 - 05:09 AM

View Posttwocandles, on 03 March 2011 - 02:39 PM, said:

...
sum( hour( timediff( date_end, date_ini ) ) ) as total_hours



This construct worked when I performed a quick test yesterday, pasting it into a $criteria->select statement (MySQL).

/Tommy
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#10 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 04 March 2011 - 05:11 AM

Problem is that if you do not use an Array like this:

$crit->select=array('...','...');


Yii will explode the select string by ','. Since there's a comma in your statement, it will not work right. So use an Array and you'll be fine.
0

#11 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 04 March 2011 - 05:18 AM

View PostMike, on 04 March 2011 - 05:11 AM, said:

Problem is that if you do not use an Array like this:

$crit->select=array('...','...');


Yii will explode the select string by ','. Since there's a comma in your statement, it will not work right. So use an Array and you'll be fine.


Actually it worked without an array when I tried it :huh: .
That's why I didn't post an answer, it has to be something obvious.

/Tommy
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#12 User is offline   twocandles 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 58
  • Joined: 23-December 10

Posted 04 March 2011 - 05:18 AM

I'll try when I get home. Thank you all for your help.
0

#13 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 04 March 2011 - 05:27 AM

@tri:
Could happen with "with" queries only. Check the source of CJoinElement::getColumnSelect(). This is where the



            if(is_string($select))
                $select=explode(',',$select);


happens. I think it's used in JOIN queries.
0

#14 User is offline   twocandles 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 58
  • Joined: 23-December 10

Posted 04 March 2011 - 05:28 AM

Maybe this behaviour has changed (fixed?) in the latest snapshots. I'm using the last stable 1.1.6... Does it has sense that the "," has anything to do with the number of "(" that appear in an expression?

I traced the code to the CActiveFinder until I found a regular expression that kind of freaked me out :) (I've never been able to fully understand them). I'll take a deeper look into it and i'll post back.
0

#15 User is offline   twocandles 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 58
  • Joined: 23-December 10

Posted 04 March 2011 - 01:17 PM

Thank you all. My code looks like this now:

$criteria->select = array( 'ifnull( sum( hour( timediff( date_end, date_ini ) ) ), 0 ) as custom1',
                            'ifnull( sum(price), 0 ) as custom2');


And I had to define $custom1 and $custom2 as public properties in my model class.
1

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