laravel7 多个表关联复杂查询

handler logic 

    	$list = DemoBaseModel::getList($siteId, $query, ['created_at' => 'desc'], [
				'attach',
				'visit',
				'extend',
				'ipAddress',
				'userOnly' => function ($sql) use ($mainUserId) {
					$sql->where('user_id', '=', $mainUserId);
				},
				'mark'     => function ($sql) use ($siteId) {
					$sql->where('site_id', '=', $siteId);
				},
			])->toArray();

 

 model logic

DemoBase::query()
            ->with($with)
            ->when(isset($where['country_id']) && !empty($where['country_id']), function ($query) use ($where) {
                $query->where(['country_id' => $where['country_id']]);
            })
            ->when(is_numeric($siteId) && !empty($siteId), function ($query) use ($siteId) {
                $query->where(['site_id' => $siteId]);
            })
            ->when(is_array($siteId) && !empty($siteId), function ($query) use ($siteId) {
                $query->whereIn('site_id', $siteId);
            })
            ->when(isset($where['is_delete']), function ($query) use ($where) {
                $query->where(['is_delete' => $where['is_delete']]);
            })
            ->when(isset($where['user_id']) && !empty($where['user_id']), function ($query) use ($where) {
                if (!isset($where['is_admin']) || !$where['is_admin']) {
                    $query->user($where['user_id'])->read($where['is_read'] ?? null);
                }
            })
            ->when(isset($where['max_msg_id']) && $where['max_msg_id'] > 0, function ($query) use ($where) {
                $query->where('msg_id', '>', $where['max_msg_id']);
            })
            ->when(isset($where['grade_type']), function ($query) use ($where) {
                switch ($where['grade_type']) {
                    default:
                    case 1:
                        $query->where('grade', '<=', 100);
                        break;
                    case 2:
                        $query->where('grade', '>', 100);
                        break;
                }
            })
            ->when(isset($where['type']) && $where['type'] >= 0, function ($query) use ($where) {
                $query->where('type', '=', $where['type']);
            })
            ->when(isset($where['source']) && $where['source'] >= 0, function ($query) use ($where) {
                $query->where('source', '=', $where['source']);
            })
            ->when(isset($where['group_id']) && !empty($where['group_id']), function ($query) use ($where) {
                $query->where('group_id', '=', $where['group_id']);
            })
            ->when(isset($where['grade']) && !empty($where['grade']), function ($query) use ($where) {
                $query->where('grade', '<=', $where['grade']);
            })
            ->when(isset($where['ip']) && !empty($where['ip']), function ($query) use ($where) {
                $query->where('ip', 'like', "%{$where['ip']}%");
            })
            ->when(isset($where['query']) && !empty($where['query']), function ($query) use ($where) {
                $query->where(function ($query) use ($where) {
                    $query->where('subject', 'like', "%{$where['query']}%")
                        ->orWhere('msg_email', 'like', "%{$where['query']}%")
                        ->orWhere('phone', 'like', "%{$where['query']}%")
                        ->orWhere('message', 'like', "%{$where['query']}%");
                });
            })
            ->when(isset($where['start_time']) && $where['start_time'], function ($query) use ($where) {
                $query->where('created_at', '>=', $where['start_time']);
            })
            ->when(isset($where['end_time']) && $where['end_time'], function ($query) use ($where) {
                $query->where('created_at', '<=', $where['end_time']);
            })
            ->when($orderBy, function ($query) use ($orderBy) {
                foreach ($orderBy as $key => $value) {
                    $query->orderBy($key, $value);
                }
            })
            ->when(isset($where['mark']) && $where['mark'], function ($query) use ($where, $siteId) {
                switch ($where['mark']) {
                    case 1:
                        $query->where('msg_email', '!=', '')
                            ->whereHas('mark', function ($query) use ($siteId) {
                                $query->where('site_id', '=', $siteId);
                            });
                        break;
                    case 2:
                        $query->where('msg_email', '!=', '')
                            ->whereDoesntHave('mark', function ($query) use ($siteId) {
                                $query->where('site_id', '=', $siteId);
                            });
                        break;
                }
            })
            ->when(isset($where['item_type']) && $where['item_type'] >= 0, function ($query) use ($where) {
                $query->where('item_type', '=', $where['item_type']);
            })
            ->when(isset($where['is_dispense']) && $where['is_dispense'] >= 0, function ($query) use ($where) {
                $query->where('is_dispense', '=', $where['is_dispense']);
            })
            ->paginate();

model    

	protected $primaryKey = 'xx_id';

    public function getIpAttribute($value)
    {
        //IpAddressModel::resave($value);

        return $value;
    }

    public function extend()
    {
        return $this->hasMany(InquireExtend::class, 'msg_id', 'msg_id');
    }

    public function user()
    {
        return $this->hasMany(InquireUser::class, 'msg_id', 'msg_id');
    }

    public function userOnly()
    {
        return $this->hasOne(InquireUser::class, 'msg_id', 'msg_id');
    }

    public function ipAddress()
    {
        return $this->hasOne(IpAddress::class, 'ip', 'ip');
    }

    public function cms()
    {
        return $this->hasMany(InquireCms::class, 'msg_id', 'msg_id');
    }

    public function visit()
    {
        return $this->hasMany(InquireVisit::class, 'msg_id', 'msg_id');
    }

    public function attach()
    {
        return $this->hasMany(InquireAttach::class, 'msg_id', 'msg_id');
    }

    public function product()
    {
        return $this->hasMany(InquireProduct::class, 'msg_id', 'msg_id');
    }

    public function group()
    {
        return $this->hasOne(InquireGroup::class, 'group_id', 'group_id');
    }

    public function mark()
    {
        return $this->hasOne(InquireEmailMark::class, 'email_suffix', 'msg_email_suffix');
    }

    public function country()
    {
        return $this->hasOne(InquireCountry::class, 'id', 'country_id');
    }

    public function scopeUser($query, $userIds)
    {
        $userIds = is_array($userIds) ? $userIds : [$userIds];
        if ($userIds) {
            $query->where(function ($query) use ($userIds) {
                $query->whereHas('user', function ($query) use ($userIds) {
                    $query->whereIn('user_id', $userIds);
                })->orWhereIn('type',[InquireBaseConstant::TYPE_WEB,InquireBaseConstant::TYPE_CHATONLINE]);
                //->orWhere('type', '=', InquireBaseConstant::TYPE_WEB);
            });
        }

        return $query;
    }

    public function scopeRead($query, $isRead = null)
    {
        return $query->when(!is_null($isRead), function ($query) use ($isRead) {
            $query->whereHas('user', function ($query) use ($isRead) {
                $query->where('is_read', '=', $isRead);
            });
        });
    }


	public function getInquireBaseTags()
    {
        return $this->hasMany(InquireBaseTags::class, 'msg_id', 'msg_id');
    }