前言-场景

我需要进行搜索,其中 名字 手机号,需要查询两个表. 这是关联了两个表出来的数据

同一个字段 关联了 2个表（主副表-结构完全一致） 进行存在判断匹配查询

// 同一个字段 关联了 2个表（主副表-结构完全一致） 进行存在判断匹配查询

            // add by bobo 2021-01-07

            $query->with('subjectInfo:id,subject_name,phone,indication,stage');

                    // ->where("subject_name", "LIKE", "%$subject_name%");

            if (!empty($subject_name)) {

                $query->whereHas('subjectInfo', function (Builder $query) use ($subject_name){

                    $query->Where('subject_name', 'like', "%$subject_name%");

                });

                // 查询条件附带从库

                $query->orWhereHas('channel_subjectInfo', function (Builder $query) use ($subject_name){

                    // 前方一个是 wherehas 实际上是转为了 a.id = b.a_id 

                    // 下面如果是or 那就变成 或者条件 这里实际上必须是and

                    $query->where('subject_name', 'like', "%$subject_name%");

                });

            }

            if (!empty($subject_phone_num)) {

                $query->whereHas('subjectInfo', function (Builder $query) use ($subject_phone_num){

                    $query->where('phone', 'like', "%$subject_phone_num%");

                });

                // 查询条件附带从库

                $query->orWhereHas('channel_subjectInfo', function (Builder $query) use ($subject_phone_num){

                    $query->where('phone', 'like', "%$subject_phone_num%");

                });

            }

sql 语句– 查询 手机号

select * from `subject_follow_up` where exists (select * from `subjects` where `subject_follow_up`.`subject_id` = `subjects`.`id` and `phone` like 8) or exists (select * from `channel_subjects` where `subject_follow_up`.`subject_id` = `channel_subjects`.`id` or (`phone` like 8)) order by `follow_up_at` asc limit 800000 offset 0

sql 语句– 查询 名字

select * from `subject_follow_up` where exists (select * from `subjects` where `subject_follow_up`.`subject_id` = `subjects`.`id` and (`subject_name` like '%2%')) or exists (select * from `channel_subjects` where `subject_follow_up`.`subject_id` = `channel_subjects`.`id` and `subject_name` like '%2%') order by `follow_up_at` asc limit 100000 offset 0

解释 wherehas 中的 where 与 orwhere

前方一个是 wherehas 实际上是转为了 a.id = b.a_id
下面如果是or 那就变成 或者条件 这里实际上必须是and

否则条件就变为这样的sql

select * from `subject_follow_up` where exists (select * from `subjects` where `subject_follow_up`.`subject_id` = `subjects`.`id` or `phone` like 8) or exists (select * from `channel_subjects` where `subject_follow_up`.`subject_id` = `channel_subjects`.`id` or (`phone` like 8)) order by `follow_up_at` asc limit 800000 offset 0

我们单独抽出来 wherehas的关联查询来看看

orwhere结果.

select * from `subjects`,subject_follow_up where `subject_follow_up`.`subject_id` = `subjects`.`id` or `phone` like 8

我们发现,phone 不是8 也出来了

where 结果:

select * from `subjects`,subject_follow_up where `subject_follow_up`.`subject_id` = `subjects`.`id` and `phone` like 8

这就查询不到,这就是正确的.

总结

wherehas 就是 where exists

where 是and

orwhere 是or

果然还是围绕着基础展开的哈哈.

本作品采用《CC 协议》，转载必须注明作者和本文链接