Laravel where, orWhereHas and whereNotIn

Question

你好，SO的伟大人物！

我希望你们今天过得愉快，身体健康

注意：我不擅长SQL

很抱歉英语不好，但我会尽力解释我的问题

我的应用程序使用Laravel v8.x，在设置了模型关系、事件、队列等之后，现在我正在为SQL 工作

ATM，我有两种型号，

• User
• Post

关系：

• User有许多Post
• User属于多个User(块(
• User属于多个User(关注(
• Post属于User

数据库：User的5条记录Block的2条记录Post的3条记录

表：(使用faker(

users

[
{ id: 1, name: 'Jonathan Beatrice', username: 'kiana.fay', ... },
{ id: 2, name: 'Lacey Kirlin', username: 'kenna.turner', ... },
{ id: 3, name: 'Alexander Schiller', username: 'cassandra95', ... },
{ id: 4, name: 'Daniel Wickozky', username: 'nkoepp', ... },
{ id: 5, name: 'Maymie Lehner', username: 'frami.felton', ... }
]

block

[
{ id: 1, by_id: 1, to_id: 2 }, // User #1 block user #2
{ id: 2, by_id: 4, to_id: 1 } // User #4 block user #1
]

posts

[
{ id: 1, user_id: 2, body: 'Test post', ... },
{ id: 2, user_id: 5, body: 'Lorem ipsum dolor sit amet ...', ... },
{ id: 3, user_id: 4, body: 'ABCD festival soon! ...', ... },
]

一切正常

现在我想实现搜索系统，我遇到了一个问题，因为我不擅长SQL

这是我的代码

SearchController.php

use ...;
use ...;
...
public function posts(Request $request)
{
// For testing purpose
$user = User::with(['userBlocks', 'blocksUser'])->find(1);
// Get all id of user that $user block
// return [2]
$user_blocks = $user->userBlocks->pluck('pivot')->pluck('to_id')->toArray();
// Get all id of user that block $user
// return [4]
$blocks_user = $user->blocksUser->pluck('pivot')->pluck('by_id')->toArray();
// Merge all ids above (must be unique())
// return [2, 4]
$blocks = array_merge($user_blocks, $blocks_user);
// .../search?q=xxx
$query = $request->query('q');
$sql = Post::query();
// Search for posts that has `posts`.`body` LIKE ? ($query)
$sql->where('body', 'LIKE', "%$query%");
// This is where I got confused
$sql->orWhereHas('user', function ($post_user) use ($blocks, $query) {
$post_user
->whereNotIn('id', $blocks) // Exclude posts that has user and their id not in (x, x, x, x, ... ; $block variable above)
->where('name', 'LIKE', "%$query%") // Find user that has name LIKE ? ($query)
->orWhere('username', 'LIKE', "%$query%"); // or Find user that has username LIKE ? ($query)
});
$sql->orderBy('created_at', 'DESC');
$sql->with(['user']);
$posts = $sql->simplePaginate(10, ['*'], 'p');
return $posts;
}

我运行代码.../search?q=e

注意：

• 所有users的名称中都有字母E
• 而且所有的posts体内都有字母E
• 我们(作为用户#1(阻止用户#2，而用户#4阻止我们(用户#1(

结果：控制器返回了所有帖子

这是我使用DB::enableQueryLog()和DB::getQueryLog()时的查询

SELECT
*
FROM
`posts`
WHERE `body` LIKE ?
AND EXISTS
(SELECT
*
FROM
`users`
WHERE `posts`.`user_id` = `users`.`id`
AND (
`id` NOT IN (?)
AND `username` LIKE ?
OR `name` LIKE ?
))
ORDER BY `created_at` ASC
LIMIT 11 OFFSET 0

目标：搜索所有具有body LIKE？的帖子？，或有用户的帖子；用户名LIKE？或者叫LIKE？(但也不包括我们阻止的用户和阻止我们的用户

提前感谢

如果有任何不清楚的解释，我会编辑它A.s.A.p

Answer 1

如果我在最近的laravel安装上运行，并对您的一个问题7.19.1版本进行了建议的更改，我会收到以下查询：

SELECT
*
FROM
`posts`
WHERE `body` LIKE ?
OR EXISTS <- line of interest
(SELECT
*
FROM
`users`
WHERE `posts`.`user_id` = `users`.`id`
AND (
`id` NOT IN (?)
AND (`username` LIKE ?
OR `name` LIKE ?) <- extra brackets ive added
))
ORDER BY `created_at` ASC
LIMIT 11 OFFSET 0

查看感兴趣的行，并将其与您的laravel版本正在运行的查询进行比较。laravel错误地生成了AND EXISTS行。OrWhereHas在您的版本中运行不正常，我找不到版本号来查看它是在哪里修复的。

如果可能的话，我建议升级到最新版本，但这并不总是一个选项。我仔细研究了一下，这个问题中的用户似乎遇到了类似的问题：

WhereHas((/或WhereHas未按预期约束查询

您可以尝试将$sql->with(['user']);移到OrWhereHas子句之前。我不确定这是否会将其更改为OR，但值得一试。第二件事，我在OR子句中添加了whereNested，以确保优先级是正确的，这在上面的查询中添加了额外的括号，就像在您不想要的中一样

(`id` NOT IN (1, 2, 3)
AND `name` LIKE % test %)
OR `username` LIKE % test %

从那时起，它将在exists子句中包含您被阻止的帖子。

所以最后的变化是这样的，我认为这符合你的描述：

$sql->with(['user']); //deleted from original position and move here
$sql->where('body', 'LIKE', "%$query%")->whereNotIn('id', $blocks); //additional line 
$sql->orWhereHas('ambience', function ($post_user) use ($blocks, $query) {
$post_user
->whereNotIn('id', $blocks); 
$post_user->whereNested(function($post_user) use ($query) { //new bit
$post_user->where('name', 'LIKE', "%$query%") 
->orWhere('username', 'LIKE', "%$query%"); 
});
});