[FIXED] how to convert query with multiple group by columns to CActiveDataProvider in Yii 1.1.14?

Issue

I need to conver this sql statement that has a multi column group by clause into CActiveDataProvider in Yii version 1.1.4?

select * from my_table_name  group by  bookname,categorytitle,bookkey order by bookname,categorytitle,bookkey asc;

I need a CActiveDataProvider to feed a CGridView search function. Apparently, I only have a simple return in my model of the search function that runs my CGridView

    return new CActiveDataProvider(get_class($this),
        array(
            'criteria' => $criteria,
            'sort' => array(
                'defaultOrder' => 'id ASC'
            ),
            'pagination' => array('pageSize' => ActiveRecord::PAGE_SIZE),
        ));

so how to convert the sql statement that I gave into CActiveDataProvider format ?

Solution

You can do using two way -

• Make sql view and than create yii model and use this in your controller or page view.

• Create custom search function in your existing model and use group by

e.g. -

<?php
public function new_search(){
  $criteria = new CDbCriteria();

  ...
  ..

  $criteria->group = "bookname, categorytitle, bookkey"; 
  $criteria->order = "bookname, categorytitle, bookkey";

  return new CActiveDataProvider($this, array(
    'criteria'=>$criteria,
    'pagination'=>array('pageSize'=>100)
  ));
}
?>

Answered By - Rohit Suthar