为什么 whereHas 多对多关系查询有时失效?
关联关系:
public function territory()
{
return $this->belongsToMany(
Territory::class,
'territory_hospital_relations',
'hospital_id',
'territory_id'
)->withTimeStamps();
}.
查询:
a.
if(!empty($titles)){$model->whereIn('title', $titles);}
b.
if(!empty($territoryIds)){
$model->whereHas('territory', function ( $query) use ($territoryIds) {
$query->whereIn('territory.id', $territoryIds);
});
}
ps:a,b同时执行的时候,筛选有时有效(下1),有时失效(下2)
打印的SQL查询:
1.
SELECT
count( * ) AS AGGREGATE
FROM
`hospitals`
WHERE
`title` IN ( 北京电力医院 )
AND EXISTS (
SELECT
*
FROM
`territory`
INNER JOIN `territory_hospital_relations`
ON `territory`.`id` = `territory_hospital_relations`.`territory_id`
WHERE
`hospitals`.`id` = `territory_hospital_relations`.`hospital_id`
AND `territory`.`id` IN ( 134 )
AND `territory`.`deleted_at` IS NULL
)
AND `hospitals`.`deleted_at` IS NULL #有结果
2.
SELECT
count( * ) AS AGGREGATE
FROM
`hospitals`
WHERE
`title` IN ( 北京电力医院 )
AND EXISTS (
SELECT
*
FROM
`territory`
INNER JOIN `territory_hospital_relations`
ON `territory`.`id` = `territory_hospital_relations`.`territory_id`
WHERE
`hospitals`.`id` = `territory_hospital_relations`.`hospital_id`
AND `territory`.`id` IN ( 84 )
AND `territory`.`deleted_at` IS NULL
)
AND `hospitals`.`deleted_at` IS NULL #没结果,如果不加title in (北京电力医院)的限制,结果集中存在territory的ID是84的数据
3.
SELECT
`territory`.*,
`territory_hospital_relations`.`hospital_id` AS `pivot_hospital_id`,
`territory_hospital_relations`.`territory_id` AS `pivot_territory_id`,
`territory_hospital_relations`.`created_at` AS `pivot_created_at`,
`territory_hospital_relations`.`updated_at` AS `pivot_updated_at`
FROM
`territory`
INNER JOIN `territory_hospital_relations`
ON `territory`.`id` = `territory_hospital_relations`.`territory_id`
WHERE
`territory_hospital_relations`.`hospital_id` IN ( 3292 ) #北京电力医院的id是3292
AND `territory`.`deleted_at` IS NULL #这条sql的结果territory的值只有84,134
推荐文章: