ACTIVE RECORD(ORM)

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

namespace appmodels;
use yiidbActiveRecord;
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

关联表数据

yiidbActiveRecord::hasMany() and yiidbActiveRecord::hasOne() 

class Customer extends yiidbActiveRecord
{
    public function getOrders()
    {
        // Customer has_many Order via Order.customer_id -> id
        return $this->hasMany(Order::className(), ["customer_id" => "id"]);
    }
}
class Order extends yiidbActiveRecord
{
    public function getCustomer()
    {
        // Order has_one Customer via Customer.id -> customer_id
        return $this->hasOne(Customer::className(), ["id" => "customer_id"]);
    }
}
class Customer extends yiidbActiveRecord
{
    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 yiidbActiveRecord
{
    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 yiidbActiveRecord
{
    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 yiimongodbActiveRecord
{
    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 appmodels;

use yiidbActiveQuery;

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

namespace appmodels;

use yiidbActiveRecord;

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 yiidbActiveRecord
{
    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 yiidbActiveRecord
{
    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();
}