Best Way To Get Data From Db

Yii 1.1 General Discussion
1

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?

2

:(

3

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.

4

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.

5

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.

6

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 table.id=:your_param 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);




$command->queryRow();
7

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")

		->queryAll();

What you think about this?

8

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