Yii CDbCommand create query

Question

I can't create the following query using Yii:

SELECT recipientId as broadcasterId, SUM(quantity) as quantity FROM `creditlog` 
WHERE websiteId=3 AND timeAdded>='2013-01-17' 
AND timeAdded<='2013-02-17' 
AND recipientId IN (10000024, 10000026, 1000028) GROUP BY `recipientId`

I tried:

$command = Yii::app()->db->createCommand();
$command->select('recipientId as broadcasterId, SUM(quantity) as quantity');
$command->from('creditlog');

$command->where('websiteId=:websiteId AND timeAdded>=:dateStart AND timeAdded<=:dateEnd AND recipientId IN (:recipients)',array(':websiteId' => $websiteId, ':dateStart' => $dateStart, ':dateEnd' => $dateEnd, ':recipients' => $broadcasterIds));

$command->group('recipientId');

also the andWhere() function which is in the docs seems to be missing.

The issue is that IN condition but I can't find a way to rewrite it.

Answer 1

Since you don't have access to andWhere, which would make life much simpler, you have to express the parameters with where like this:

$command->where(array(
    array('and', 
          'websiteId=:websiteId',
          array('and', 
                'timeAdded>=:dateStart',
                 array('and',
                       // ...

), $parameters);

This is done so that you can at some point use the proper array('in', 'recipientId', $values) syntax to produce the IN(...) SQL.

However, this is ugly and difficult to manage. As long as all the conditions are simply joined together with AND you can construct the data structure programmatically from a saner data representation like this (in effect this is a workaround for the missing andWhere):

$conditions = array(
    'websiteId=:websiteId',
    'timeAdded>=:dateStart',
    'timeAdded<=:dateEnd',
    array('in', 'recipientId', $broadcasterIds),

);

$where = null;
foreach ($conditions as $condition) {
    if (!$where) {
        $where = $condition;
    }
    else {
        $where = array('and', $where, $condition);
    }
}

$command->where($where, $parameters);

For more information on why this way of expressing things has to be used you can refer to the documentation for CDbCommand::where.