Which method for building SQL query?

Hi everyone,

I have a general question about best Practice. In my website I want to build a SQL query according to a certain number of Criterias and I wonder which method is the best to use. So far I use the 2 following methods:

1/ In my Controller I use an instance of a Comment model that I initialize with my selected criterias and that I use to build my query.




$model = new Comment; 

$model->idUser = isset($_GET[‘idUser’]) ? $_GET['idUser'] : null;     

$model->idCategory = isset($_GET['idCategory']) ? $_GET['idCategory'] : null;     

$model->order = isset($_GET['order']) ? $_GET['order'] . ' DESC' : null;   

$model->limit = isset($_GET[‘limit’]) ? $_GET[‘limit’]: null;   

$criteria = $model->getCriteria();

$datas = $model->findAll($criteria)

;

In my Comment model I have the function:




public function getCriteria()

{

            $criteria = new CDbCriteria;

            $criteria->condition = ' 1 ';

            $criteria->with = array('user','categories'); 

            $criteria->params = array();                       


            $criteria->order = isset($this->order)? "$this->order DESC " : " t.voteUp DESC ";


            $criteria->limit = isset($this->limit)? $this->limit : 10;


            if(isset($this->idUser)){

                $criteria->condition .= ' AND t.idUser = :idUser ';

                $criteria->params[":idUser"] = $this->idUser;

            }


            if(isset($this->idCategory)){

                $criteria->condition .= ' AND t.idCategory = :idCategory ';

                $criteria->params[":idCategory"] = $this->idCategory;

            }


            return $criteria;

}



2/ Or the second method:




$model = new Comment;

$criteria = new CDbCriteria;

$criteria->order = isset($_GET['order']) ? $_GET['order'] . ' DESC' : 't.voteUp  DESC';   

$criteria->limit = isset($_GET[‘limit’]) ? $_GET[‘limit’]:10;   

$idCategory = isset($_GET['idCategory']) ? $_GET['idCategory'] : null;     

$idUser = isset($_GET[‘idUser’]) ? $_GET['idUser'] : null;     

$datas = $model->category($idCategory)->user($idUser)->with('user'),'categories')->findAll($criteria);



And in my Comment model I have the following Parameterized Named Scope:




        public function category($idCategory)

        {

            if(!$idCategory === null)

                $this->getDbCriteria()->mergeWith(array(

                    'condition'=>"t.idCategory=$idCategory ",

                ));

            return $this;

        }


        public function user($idUser)

        {

            if(!$idUser === null)

                $this->getDbCriteria()->mergeWith(array(

                    'condition'=>"t.idUser=$idUser ",

                ));

            return $this;

        }



Those are just examples, there could be some mistakes in my code but it’s just for the general understanding of the method. Also some pages could have more than 10 criterias, so I find it not very convenient to use Scopes in that case because we would have in the Controller something like :


$datas = $model->….->….->….->…->…->…->…->…->…->…->…->…->…->…->…->findAll($criteria);

I am not very sure about the efficiency of the methods that I use so I would like to get some other people’s opinions… Or is there any better way to do?

Thanks in advance