There are several ways to connect to the database in yii

PDO method (suitable for multi-table connection queries).               (Recommended learning: yii framework)

$sql = "";//原生态sql语句 
xx::model()->dbConnection->createCommand($sql)->execute();  非select语句(update、insert、delete)
xx::model()->dbConnection->createCommand($sql)->queryRow(); 查询select一条记录
xx::model()->dbConnection->createCommand($sql)->queryAll(); 查询select多条记录

$sql = "select a.*, count(b.role_id)as num from {{user_role}} a left join {{user}} b on a.id = b.role_id group by b.role_id order by a.id";
$user_role_info = UserRole::model()->dbConnection->createCommand($sql)->queryAll(); 
$this->render("list", array('user_role_info'=>$user_role_info));

For example

$sql = "select a.*, count(b.role_id)as num from slgo_user_role a left join slgo_user b on a.id = b.role_id group by b.role_id order by a.id";
$user_role_info = UserRole::model()->dbConnection->createCommand($sql)->queryAll(); 
$this->render("list", array('user_role_info'=>$user_role_info));

Active Record method

(1)New

$post=new Post; 
$post->title='sample post'; 
$post->content='post body content'; 
$post->save();

Criteria method

You can also use $condition to specify more complex query conditions. Instead of using strings, we can make $condition an instance of CDbCriteria, which allows us to specify conditions that are not limited to WHERE.

$criteria=new CDbCriteria; 
$criteria->select='title';  // 只选择 'title' 列 
$criteria->condition='postID=:postID'; 
$criteria->params=array(':postID'=>10); 
$post=Post::model()->find($criteria);

An alternative to CDbCriteria is to pass an array to the find method. The keys and values ​​of the array respectively correspond to the attribute names and values ​​of the criterion

The above example can be rewritten as follows:

$post=Post::model()->find(array( 
    'select'=>'title', 
    'condition'=>'postID=:postID', 
    'params'=>array(':postID'=>10), 
));

When a query condition is about pressing When the specified value matches several columns, we can use findByAttributes(). We make the $attributes parameter an array of values ​​indexed by column names.

In some frameworks, this task can be achieved by calling a method like findByNameAndTitle.

Although this approach looks tempting, it often causes confusion, conflicts and issues such as case sensitivity of column names.

Query Builder method

$user = Yii::app()->db->createCommand() 
    ->select('id, username, profile') 
    ->from('tbl_user u') 
    ->join('tbl_profile p', 'u.id=p.user_id') 
    ->where('id=:id', array(':id'=>$id)) 
    ->queryRow();

1. When making good use of ActiveRecord, that is, do not let it generate the second SQL query method. From my experience, do not overdo it. Pay attention to the difference in time and performance. For big data queries, in terms of memory space, you can use toArray() in yii2 to save memory consumption.

2. The advantage of ActiveRecord over PDO lies in its convenience.

One is that compared to writing SQL statements, using ActiveRecord is simpler, and more importantly, it is less error-prone.

Second, it provides many conveniences beyond SQL statements, such as parameter filtering, binding, etc. You will always encounter this in web development. Writing one SQL statement after another, the degree of code reuse is not high, and it is easy to forget to filter somewhere, causing security risks.

3. Yii2 already has Query, which is better in performance than ActiveRecord and can be used as a replacement. Of course, convenience and efficiency are two sides of the coin, and its use is not as convenient as ActiveRecord.

But my experience in the project is that Query is only used slightly less frequently than ActiveRecord. Personally, I think Query/ActiveQuery is an exciting new feature introduced in Yii2.

4. The previous articles talk about the application scenarios of ActiveRecord. For PDO, if you want to use it, it should be for more complex SQL operations, such as one or two sentences that cannot be written clearly.

In this case, using ActiveRecord to build is no less complex than writing SQL directly, so you can consider using PDO. But in my impression, if the SQL statements used in the project are very complex, they will be written as DB Views or stored procedures. Therefore, I feel that PDO is not used much, it can be said to be very little.

5. If we pursue higher efficiency, I personally don’t think PDO should be used too much, which makes later maintenance extremely difficult. Instead, you can consider optimizing SQL queries, optimizing indexes and table structures, using cache, etc. It is worth mentioning that using cache is the simplest, most direct, and most effective method.

6. There is never any need to consider performance issues prematurely

The above is the detailed content of There are several ways to connect to the database in yii. For more information, please follow other related articles on the PHP Chinese website!