在开发过程中会遇到多表关联查询的情况,这次使用三个表做关联查询
表1:rate
表2:rate_acfee
表3:rate_acfee_info
表1的一条数据对应着表2的多条数据,表2的一条数据对应着表3的多条数,使用方法如下
表1模型Rate:
/**
* 费率Model
* Class Producer
* @package app\model\Producer
*/
class Rate extends BaseModel{
use ModelTrait;
use SoftDelete;
protected $name = 'rate';
/**
* 数据表主键
* @var string
*/
protected $pk = 'id';
/*
*开启软删除
*/
protected $createTime = 'add_time';
protected $deleteTime = 'delete_time';
protected $updateTime = 'update_time';
public function rateAcfee(){
return $this->hasMany('RateAcfee','rate_id','id'); //rate_id 为表2(关联表1主键字段) id 为表1主键
}
}
表2模型RateAcfee:
/** * Model * Class RateAcfee */ class RateAcfee extends BaseModel{ use ModelTrait; //use SoftDelete; protected $name = 'rate_acfee'; /** * 数据表主键 * @var string */ protected $pk = 'id'; protected $createTime = 'add_time'; public function times(){ return $this->hasMany('RateAcfeeInfo','fee_id','id'); // RateAcfeeInfo为表3模型, fee_id为表3(关联表2主键字段)id 为表2主键
} }
接下来就可以来使用控制器或是模型写方法进行连贯三表查询:
$model = new Rate; $list = $model->with(['rateAcfee.times' => function($query) { // .times 查询表3的关键 $query->order('id'); }])->select(); return json($list);
查询结果: