coupon_cate 分类表
coupon_brand 品牌表
coupon_brand_cate_relation 分类和品牌 =》 多对多关系表
代码如下
/** * @descripe 关联关系-品牌 */public function couponBrand(){return $this->belongsToMany(CouponBrand::class,'coupon_brand_cate_relation','brand_id','cate_id');}
多对多关联查询 带where其他条件
$data = CouponCate::query()->where('coupon_cate.status', CouponCate::STATUS_ACTIVE)->with(['couponBrand' => function($query) {// $query->getQuery() 需要取出原先的查询条件在拼上新的where条件$query->getQuery()->where('coupon_brand.status', CouponBrand::STATUS_ACTIVE)->order('sort desc'); }]) ->select()->toArray();
with输出的sql语句
SELECT `coupon_brand`.*,`pivot`.`cate_id` AS `pivot__cate_id`,`pivot`.`brand_id` AS `pivot__brand_id` FROM `coupon_brand` INNER JOIN `coupon_brand_cate_relation` `pivot` ON `pivot`.`brand_id`=`coupon_brand`.`id` WHERE `coupon_brand`.`status` = '1' AND `pivot`.`cate_id` IN ('1','2','3','4','5','6','7','8','9') ORDER BY `sort` DESC
闭包查询 中的dump($query->getQuery());
关键点 $query->getQuery() 取出原先的查询条件 在拼上where的其他条件
注意以下这种写法会覆盖掉原先的where查询条件
$data = CouponCate::query()->where('coupon_cate.status', CouponCate::STATUS_ACTIVE) ->with(['couponBrand' => function($query){ //todo 覆盖where条件,导致多对多查询无效,返回的是最后一条数据 $query->where('coupon_brand.status', CouponBrand::STATUS_ACTIVE); }])
输出的sql语句 SELECT `coupon_brand`.*,`pivot`.`cate_id` AS `pivot__cate_id`,`pivot`.`brand_id` AS `pivot__brand_id` FROM `coupon_brand` INNER JOIN `coupon_brand_cate_relation` `pivot` ON `pivot`.`brand_id`=`coupon_brand`.`id` WHERE `pivot`.`cate_id` = '9' AND `coupon_brand`.`status` = '1'
学习笔记,如有侵权,请联系删除!