Yii2 数据库Active Record(ORM)

ACTIVE RECORD(ORM)

参考:http://www.yiiframework.com/doc-2.0/guide-db-active-record.html

namespace app\models;
use yii\db\ActiveRecord;
class Customer extends ActiveRecord
{
	const STATUS_ACTIVE = 'active';
	const STATUS_DELETED = 'deleted';
	public static function tableName()
	{
		return 'customer';
	}
	public static function getDb()
	{
		return \Yii::$app->db2;  // use the "db2" application component
	}
	public static function init() //自定义初始默认数据
	{
	    parent::init();
	    $this->status = self::STATUS_ACTIVE;
	}
}

访问数据列

$id = $customer->id;
$email = $customer->email;
-------------
$customer->email = 'jane@example.com';
$customer->save();

查询数据

$customers = Customer::find()
    ->where(['status' => Customer::STATUS_ACTIVE])
    ->orderBy('id')
    ->all();
$customer = Customer::find()
    ->where(['id' => 1])
    ->one();
$count = Customer::find()
    ->where(['status' => Customer::STATUS_ACTIVE])
    ->count();
$customers = Customer::find()->indexBy('id')->all();
$sql = 'SELECT * FROM customer';
$customers = Customer::findBySql($sql)->all();
// to return a single customer whose ID is 1:
$customer = Customer::findOne(1);
Customer::find()->where(['status' => Customer::STATUS_ACTIVE])->limit(1)->one()

//返回数组
$customers = Customer::find()
    ->asArray()
    ->all();

批量返回

// fetch 10 customers at a time
foreach (Customer::find()->batch(10) as $customers) {
    // $customers is an array of 10 or fewer Customer objects
}
// fetch 10 customers at a time and iterate them one by one
foreach (Customer::find()->each(10) as $customer) {
    // $customer is a Customer object
}
// batch query with eager loading
foreach (Customer::find()->with('orders')->each() as $customer) {
}

数据处理

• save()
• insert()
• update()
• delete()

批量数据处理

• updateCounters()
• updateAll()
• updateAllCounters()
• deleteAll()

// to insert a new customer record
$customer = new Customer();
$customer->name = 'James';
$customer->email = 'james@example.com';
$customer->save();  // equivalent to $customer->insert();


// to update an existing customer record
$customer = Customer::findOne($id);
$customer->email = 'james@example.com';
$customer->save();  // equivalent to $customer->update();

// to delete an existing customer record
$customer = Customer::findOne($id);
$customer->delete();

// to delete several customers
Customer::deleteAll('age > :age AND gender = :gender', [':age' => 20, ':gender' => 'M']);

// to increment the age of ALL customers by 1
Customer::updateAllCounters(['age' => 1]);

数据效验

$model = Customer::findOne($id);
if ($model === null) {
    throw new NotFoundHttpException;
}
if ($model->load(Yii::$app->request->post()) && $model->save()) {
    // the user input has been collected, validated and saved
}else{
   ;
}

初始默认数据

$customer = new Customer();
$customer->loadDefaultValues();

生命与执行周期

初始化

constructor
init(): will trigger an EVENT_INIT event

调用 save()时

beforeValidate(): //return bool
afterValidate(): will trigger an EVENT_AFTER_VALIDATE event
beforeSave(): will trigger an EVENT_BEFORE_INSERT or EVENT_BEFORE_UPDATE event
perform the actual data insertion or updating
afterSave(): will trigger an EVENT_AFTER_INSERT or EVENT_AFTER_UPDATE event

调用delete()删除时

beforeDelete(): will trigger an EVENT_BEFORE_DELETE event
perform the actual data deletion
afterDelete(): will trigger an EVENT_AFTER_DELETE event

关联表数据

yii\db\ActiveRecord::hasMany() and yii\db\ActiveRecord::hasOne() 

class Customer extends \yii\db\ActiveRecord
{
    public function getOrders()
    {
        // Customer has_many Order via Order.customer_id -> id
        return $this->hasMany(Order::className(), ['customer_id' => 'id']);
    }
}
class Order extends \yii\db\ActiveRecord
{
    public function getCustomer()
    {
        // Order has_one Customer via Customer.id -> customer_id
        return $this->hasOne(Customer::className(), ['id' => 'customer_id']);
    }
}
class Customer extends \yii\db\ActiveRecord
{
    public function getBigOrders($threshold = 100)
    {
        return $this->hasMany(Order::className(), ['customer_id' => 'id'])
            ->where('subtotal > :threshold', [':threshold' => $threshold])
            ->orderBy('id');
    }
}
$orders = $customer->getBigOrders(200)->all();

中间关联表
via() or viaTable()

class Order extends \yii\db\ActiveRecord
{
    public function getItems()
    {
        return $this->hasMany(Item::className(), ['id' => 'item_id'])
            ->viaTable('order_item', ['order_id' => 'id']);
    }
}

贪婪模式

// SQL executed: SELECT * FROM customer WHERE id=1
$customer = Customer::findOne(1);
// SQL executed: SELECT * FROM order WHERE customer_id=1
$orders = $customer->orders;
// no SQL executed
$orders2 = $customer->orders;
------------
$customers = Customer::find()->limit(100)->all();

foreach ($customers as $customer) {
    // SQL executed: SELECT * FROM order WHERE customer_id=...
    $orders = $customer->orders;
    // ...handle $orders...
}
---------------
// SQL executed: SELECT * FROM customer LIMIT 100;
//               SELECT * FROM orders WHERE customer_id IN (1,2,...)
$customers = Customer::find()->limit(100)
    ->with('orders')->all();

foreach ($customers as $customer) {
    // no SQL executed
    $orders = $customer->orders;
    // ...handle $orders...
}
-----------------------
$customer = Customer::findOne(1);
// lazy loading: SELECT * FROM order WHERE customer_id=1 AND subtotal>100
$orders = $customer->getOrders()->where('subtotal>100')->all();

// eager loading: SELECT * FROM customer LIMIT 100
//                SELECT * FROM order WHERE customer_id IN (1,2,...) AND subtotal>100
$customers = Customer::find()->limit(100)->with([
    'orders' => function($query) {
        $query->andWhere('subtotal>100');
    },
])->all();

联合查询关联表

// join with multiple relations
// find the orders that contain books and were placed by customers who registered within the past 24 hours
$orders = Order::find()->innerJoinWith([
    'books',
    'customer' => function ($query) {
        $query->where('customer.created_at > ' . (time() - 24 * 3600));
    }
])->all();
// join with sub-relations: join with books and books' authors
$orders = Order::find()->joinWith('books.author')->all();

class User extends ActiveRecord
{
    public function getBooks()
    {
        return $this->hasMany(Item::className(), ['owner_id' => 'id'])->onCondition(['category_id' => 1]);
    }
}
// SELECT user.* FROM user LEFT JOIN item ON item.owner_id=user.id AND category_id=1
// SELECT * FROM item WHERE owner_id IN (...) AND category_id=1
$users = User::find()->joinWith('books')->all();
// find all orders that contain books, but do not eager load "books".
$orders = Order::find()->innerJoinWith('books', false)->all();
// which is equivalent to the above
$orders = Order::find()->joinWith('books', false, 'INNER JOIN')->all()
//额外条件
class User extends ActiveRecord
{
    public function getBooks()
    {
        return $this->hasMany(Item::className(), ['owner_id' => 'id'])->onCondition(['category_id' => 1]);
    }
}

操作关系
link() and unlink()

$customer = Customer::findOne(1);
$order = new Order();
$order->subtotal = 100;
$customer->link('orders', $order);
$customer->save();

Cross-DBMS

// Relational database Active Record
class Customer extends \yii\db\ActiveRecord
{
    public static function tableName()
    {
        return 'customer';
    }

    public function getComments()
    {
        // Customer, stored in relational database, has many Comments, stored in MongoDB collection:
        return $this->hasMany(Comment::className(), ['customer_id' => 'id']);
    }
}

// MongoDb Active Record
class Comment extends \yii\mongodb\ActiveRecord
{
    public static function collectionName()
    {
        return 'comment';
    }

    public function getCustomer()
    {
        // Comment, stored in MongoDB collection, has one Customer, stored in relational database:
        return $this->hasOne(Customer::className(), ['id' => 'customer_id']);
    }
}

过滤

namespace app\models;

use yii\db\ActiveQuery;

class CommentQuery extends ActiveQuery
{
    public function active($state = true)
    {
        $this->andWhere(['active' => $state]);
        return $this;
    }
}

namespace app\models;

use yii\db\ActiveRecord;

class Comment extends ActiveRecord
{
    /**
     * @inheritdoc
     * @return CommentQuery
     */
    public static function find()
    {
        return new CommentQuery(get_called_class());
    }
}

$comments = Comment::find()->active()->all();
$inactiveComments = Comment::find()->active(false)->all();

class Post extends \yii\db\ActiveRecord
{
    public function getActiveComments()
    {
        return $this->hasMany(Comment::className(), ['post_id' => 'id'])->active();

    }
}

$posts = Post::find()->with([
    'comments' => function($q) {
        $q->active();
    }
])->all();

//默认
public static function find()
{
    return parent::find()->where(['deleted' => false]);
}

事务

class Post extends \yii\db\ActiveRecord
{
    public function transactions()
    {
        return [
            'admin' => self::OP_INSERT,
            'api' => self::OP_INSERT | self::OP_UPDATE | self::OP_DELETE,
            // the above is equivalent to the following:
            // 'api' => self::OP_ALL,
        ];
    }
}

$model=Post::model();
$transaction=$model->dbConnection->beginTransaction();
try
{
    // 查找和保存是可能由另一个请求干预的两个步骤
    // 这样我们使用一个事务以确保其一致性和完整性
    $post=$model->findByPk(10);
    $post->title='new post title';
    $post->save();
    $transaction->commit();
}
catch(Exception $e)
{
    $transaction->rollBack();
}