Best Way To Get Data From Db

I need to do some complex queries in the database, and I’m trying to understand what the best way to do that. Why should I use CDbCriteria instead of Query Builder? I used the Query Builder and it was quite easy, but I’m in doubt whether I should use CDbCriteria. Can anyone tell me the difference if there is one?


CDbCriteria is generally used when populating active record models. The command builder and PDO SQL statements are for when you’re aiming to retrieve a custom set of data or when you’re building a query that’s too difficult to represent using CDbCriteria.

There are also differences in performance, but whether this is a significant factor will depend on your specific scenario.

Thanks for the explanation. Which has the best performance considering two identical queries? I think in my specific scenario, the Query Builder is the way.

The best performance will come from writing your own SQL and quering with PDO directly. I personally don’t use the query builder because I find plain SQL more readable when I have to deal with complex queries.

I imagine that the query builder offers performance close to that of native SQL, so just use whichever you’re most comfortable with.

If you want max performance and security especially for comblex queries you could use Executing SQL Statements like this

$command = Yii::app()->db->createCommand('SELECT * FROM 

            ... your comblex or not query ...

            WHERE AND table2.attribute=:your_param2


                $command->bindValue(':your_param', $this->your_param);

                $command->bindValue(':your_param2', $this->your_param2);

//OR if you want to run more than one time with another values without set again the query or the values

                $command->bindParam(':your_param', $this->your_param);

                $command->bindParam(':your_param2', $this->your_param2);


Looks good as well.

I’m trying something like this:

$query = Yii::app()->db->createCommand()

		->select("c1.column_1, c1.column_2, c2.column_1, c2.column_2")

		->from("db.table t")

		->leftJoin("db.table2 t2", "(t2.column_1 = c1.columns1 AND t2.column_2 = :param)", array(":param"=>$param))

		->where(array("and", "c1.column_1 NOT IN (SELECT id FROM db.table3 WHERE id != :param_2)", "c2.column_2 IS null"), array(':param_2'=>$param_2))

		->order("column_1 desc")


What you think about this?

SQL directly with DAO is more faster and consuming fewer resources than Query Builder,

but the Query Builder is more object oriented way to execute a query and you could have less bugs in query.

on the other hand with SQL - DAO you can make as comblex sql you want!

Pick up one of these ways (including the Active Record queries) according to the action requirements.

Best regards