对着官方的看
使用关联数据(Working with Relational Data)
声明关联关系(Declaring Relations)
下面声明是1V1,1Vn的关系
public function getOrders()
{
return $this->hasMany(Order::className(), ['customer_id' => 'id']);
}
public function getCustomer()
{
return $this->hasOne(Customer::className(), ['id' => 'customer_id']);
}
先附表的主键,后主表的主键。
访问关联数据(Accessing Relational Data)
// SELECT * FROM `customer` WHERE `id` = 123
$customer = Customer::findOne(123);
下面是返回值:
common\exmodels\OrderCar Object
(
用途:1.用这个对象去查对应的数据,2.取值
$obj = $customer->getOrders(); // 返回 ActiveQuery 类的实例 此处为上面定义好的function
====================================================
// SELECT * FROM `order` WHERE `customer_id` = 123
// $orders 是由 Order 类组成的数组
$orders = $customer->orders;
$customer->orders; // 获得 `Order` 对象的数组 此处为上面定义好的function简写
下面是返回值:
yii\db\ActiveQuery Object
(
用途:1.用这个对象去查对应的数据,2.取值
比如 动态关联查询(Dynamic Relational Query)
$obj->where(['>', 'subtotal', 200])
->orderBy('id')
->all();
============================
动态关联查询(Dynamic Relational Query)
省事写法:
public function getBigOrders($threshold = 100) // 老司机的提醒:$threshold 参数一定一定要给个默认值
{
return $this->hasMany(Order::className(), ['customer_id' => 'id'])
->where('subtotal > :threshold', [':threshold' => $threshold])
->orderBy('id');
}
// SELECT * FROM `order` WHERE `customer_id` = 123 AND `subtotal` > 200 ORDER BY `id`
$orders = $customer->getBigOrders(200)->all();
// SELECT * FROM `order` WHERE `customer_id` = 123 AND `subtotal` > 100 ORDER BY `id`
$orders = $customer->bigOrders;
中间关联表(Relations via a Junction Table)
class Order extends ActiveRecord
{
public function getItems()
{
return $this->hasMany(Item::className(), ['id' => 'item_id'])
->viaTable('order_item', ['order_id' => 'id']);
}
}
class Order extends ActiveRecord
{
public function getOrderItems()
{
return $this->hasMany(OrderItem::className(), ['order_id' => 'id']);
}
public function getItems()
{
return $this->hasMany(Item::className(), ['id' => 'item_id'])
->via('orderItems');
}
}
// SELECT * FROM `order` WHERE `id` = 100
$order = Order::findOne(100);
// SELECT * FROM `order_item` WHERE `order_id` = 100
// SELECT * FROM `item` WHERE `item_id` IN (...)
// 返回 Item 类组成的数组
$items = $order->items;
延迟加载和即时加载(Lazy Loading and Eager Loading)
其他时候一般是用不上的,连接多表的时候可以考虑。
关联关系的 JOIN 查询(Joining with Relations)
通过调用 yii\db\ActiveQuery::with() 方法,你使 Active Record 在一条 SQL 语句里就返回了这 100 位客户的订单。 结果就是,你把要执行的 SQL 语句从 101 减少到 2 条!
$customers = Customer::find()
->select('customer.*')
->leftJoin('order', '`order`.`customer_id` = `customer`.`id`')
->where(['order.status' => Order::STATUS_ACTIVE])
->with('orders')
->all();
$customers = Customer::find()
->joinWith('orders')
->where(['order.status' => Order::STATUS_ACTIVE])
->all();
1对多或者1对1的情况下,通过joinwith关联关系可以用in来实现的联表操作,而避免多联表,结果以多维数组或者数组的形式返回。
下面这种的话,就是分成3步
1.查出主表Customer关联好其他附表的主键id
2.用主表的主键id当条件,作为附表的in查询
3.获得的结果以主键id相同的数组组装好返回。
例子:
// SELECT `customer`.* FROM `customer`
// LEFT JOIN `order` ON `order`.`customer_id` = `customer`.`id` AND `order`.`status` = 1
//
// SELECT * FROM `order` WHERE `customer_id` IN (...)
$customers = Customer::find()->joinWith([
'orders' => function ($query) {
$query->onCondition(['order.status' => Order::STATUS_ACTIVE]);
},
])->all();