Yii add one more select to CDbCriteria

Question

I'm relatively new to Yii.

I fell confident with raw SQL but still get a bit lost when it comes to ORM. So this may be a dummy question.

I've retrieved all necessary records building such CDbCriteria:

$criteria = new CDbCriteria(array(
  'select' => 'sum(items) as items',
  // 'condition' => 't.items > 0 and order.storage = "'Product::STORAGE_LOCAL . '"',

  'condition' => 't.items > 0 and order.storage = "' .  Product::STORAGE_LOCAL . '"',
  'order' => 'sum(items) DESC',
  'with' => array(
    'product' => array(
       'select' => 'code, title, producer, local_rest',

        **// 'select' => 'code, title, producer, sum(local_rest) as   local_rest',**
       'group' => 'product.code',
    )
  ),

  'join' => 'inner join `order` `order` on `t`.`order_id` = `order`.`id`',
   // 'group' => '`product`.`code`, `product`.`title`',
   'group' => '`product`.`code`',
   'together' => true
));

I try to get sum of local_rest field doing group by. Unfortunately it does not return what it should be.

This is how I tried to build in it into CDbCriteria:

// 'select' => 'code, title, producer, sum(local_rest) as local_rest',. - no luck.

I can get the it using separated query as:

$sum_local_rest = Yii::app()->db->createCommand("
  SELECT  id,code, title, sum(local_rest) as sum_rest FROM product GROUP BY code
  ORDER BY `sum_rest`  DESC
");

One more caution - there are duplicate records in product table. I.e. we have the same product more than one time. But if I use GROUP BY it helps to delimiter this shortcoming. This is due to bad DB design and should be fixed in the future.

The problem is that I need somehow to bind it with CDbCriteria, because it's used by CDataProvider and CDataProvider is used by GridView.

Any tips how to connect these two question in one CDbCriteria?

Thanks in advance

EDIT

Looking at the current answers I feel I need summarize. The main problem is that I need to tell CDbCriteria to retrieve records (bound by HAS_Many connections) and to calculate SUM of all these records and to make CDbCriteria to do GROUP BY of these records. No other way. I can't do it explicitly. Because I pass CDbCriteria to CDataProvider and it should run queries. This is how things work in Yii (as far as I understand).

Answer 1

//You can merge your criteria like here:

$criteria = new CDbCriteria();  //First criteria
$criteria_2 = new CDbCriteria();  //Second criteria

$criteria->mergeWith($criteria_2); //Merge criteria and criteria_2

SomeModel::model()->findAll($criteria); //Find by criteria

Answer 2

I don't see why something like this shouldn't work:

$criteria = new CDbCriteria; $criteria->select = array( "SUM(t.items) as items", "SUM(product.local_rest) as product_local_rest" ); $criteria->condition = "t.items > 0 and order.storage = "' . Product::STORAGE_LOCAL . '"'; $criteria->join = 'inner join `order` `order` on `t`.`order_id` = `order`.`id`'; $criteria->with = "product"; $criteria->group = "product.code"; $criteria->together = true;

Since you're setting together to true, the columns of your relation should be available to your query, aliased by the relation's name (product). (Note: to access the result of SUM(product.local_rest) on the models returned by CActiveDataProvider, you'll need to set $product_local_rest as a public property on the class of the returned models.)

Alternatively, if you're more comfortable writing raw SQL, you could use CDbCommand to generate an array of results, and then use CArrayDataProvider instead of CActiveDataProvider. http://www.yiiframework.com/doc/api/1.1/CArrayDataProvider

Answer 3

You also don't have to pass all elements to criteria. Try to split criteria into more code like this:

 $criteria = new CDbCriteria();
 $criteria->select = 'sum(items) as items, ' . Product::STORAGE_LOCAL;
 $criteria->condition = 't.items > 0 and order.storage = ' .  Product::STORAGE_LOCAL;

//etc.