php Laravel - 如果值包含某个字符串（取自搜索输入），则查询模型

Laravel - Query Model if values contain a certain string (taken from search input)

提问by Codearts

I am implementing a search using Laravel and Ajax. So I have a Product which belongs to a Tag and a Subcategory. On the other hand the Subcategory belongs to a Category. I want to check all of their properties (field values) and check if they contain the given string. With some searching I found out that I have to use LIKE. Here is what I tried:

我正在使用 Laravel 和 Ajax 实现搜索。所以我有一个属于标签和子类别的产品。另一方面，子类别属于类别。我想检查它们的所有属性（字段值）并检查它们是否包含给定的字符串。通过一些搜索，我发现我必须使用LIKE. 这是我尝试过的：

$products = Product::where('name_en', 'LIKE', $search)->get();

However this will get the products if the search string matches exactly the value. I want to match if it contains it. How can I proceed with the belongsTo relationships? How can I check the propreties of Tag and Subcategory as well? How to chain everything together so I achieve the desired result? Thanks in advance.

但是，如果搜索字符串与值完全匹配，这将获得产品。我想匹配它是否包含它。我该如何处理belongsTo 关系？如何检查标签和子类别的属性？如何将所有内容链接在一起以达到预期的结果？提前致谢。

回答by

you are doing one thing wrong, your query returns you exact matches because you given the exact string. But your query should be like this.

你做错了一件事，你的查询返回精确匹配，因为你给出了精确的字符串。但是你的查询应该是这样的。

$products = Product::where('name_en', 'LIKE', '%'.$search.'%')->get();

Above query will gives your products which contains the searched string.

以上查询将提供包含搜索字符串的产品。

And if you want to search in relational tables then you can user laravel method join(). But there are one more method whereHasbut I always avoiding this method, because it creates very complex query. which is very heavy. So you can use join()method which will add inner joinwith relational table.

如果你想在关系表中搜索，那么你可以使用 laravel 方法join()。但是还有一种方法，whereHas但我总是避免使用这种方法，因为它会创建非常复杂的查询。这是非常重的。所以你可以使用join()将添加inner join关系表的方法。

Here is the example of join:

下面是加入的例子：

$products = Product::join('tags', function($builder) {
                        $builder->on('tags.id', '=', 'products.tag_id');
                        // here you can add more conditions on tags table.
                    })
                    join('sub_categories', function($builder) {
                        $builder->on('sub_categories.id', '=', 'products.tag_id');
                        // here you can add more conditions on subcategories table.
                    })
                    ->where('name_en', 'LIKE', '%'.$search.'%')
                    ->get();

This is the basic example, you can use this according to your requirement.

这是基本示例，您可以根据需要使用它。

回答by Martin Bean

To add to Lakhwinder Singh's answer, it might be worth wrapping it up in a scope that you can apply to your model:

要添加到Lakhwinder Singh的答案中，可能值得将其包含在可以应用于模型的范围中：

class Product extends Model
{
    public function scopeSearch($query, $keywords)
    {
        return $query->where('name_en', 'LIKE', '%'.$keywords.'%');
    }
}

You can then use this scope like this:

然后你可以像这样使用这个范围：

$products = Product::search($keywords)->get();

Which means you don't have to keep manually adding “LIKE” conditions throughout your application.

这意味着您不必在整个应用程序中手动添加“LIKE”条件。

As an aside, Laravel's introducing Scout, a driver-based full text search extension for Eloquent, in version 5.3.

顺便说一句，Laravel 在 5.3 版本中引入了 Scout，这是一个基于驱动程序的 Eloquent 全文搜索扩展。

回答by Akram Wahid

What you want is to write an advanced query to search product based on related models too, so as previous suggestion by others, you have to write join statements.

你想要的是写一个高级查询来根据相关模型搜索产品，所以根据其他人之前的建议，你必须编写join语句。

Check my example code below, which is written to search members, the search string also will bring members if the string matches, members skills or positions, so this will surely help you.

检查我下面的示例代码，它是为搜索成员编写的，如果字符串匹配，搜索字符串也会带来成员，成员技能或职位，所以这肯定会对你有所帮助。

$users = User::select('app_users.*')
            ->distinct()
            ->join('app_members', 'app_users.id', '=', 'app_members.app_users_id')
            ->leftJoin('app_members_jobtitles', 'app_members.id', '=', 'app_members_jobtitles.app_members_id')
            ->leftJoin('app_jobtitles', 'app_members_jobtitles.app_jobtitles_id', '=', 'app_jobtitles.id')

            ->leftJoin('app_members_tags', 'app_members.id', '=', 'app_members_tags.app_members_id')
            ->leftJoin('app_technologies', 'app_members_tags.app_technologies_id', '=', 'app_technologies.id')
            ->whereNull('app_users.activation')
            ->where('app_users.block','=',0)
            ->where(function ($query)use ($search) {
                $query->orWhere('app_users.first_name', 'like', '%'.$search.'%')
                      ->orWhere('app_users.last_name', 'like', '%'.$search.'%')
                      ->orWhere('app_members.company', 'like', '%'.$search.'%')
                      ->orWhere('app_members.job_title', 'like', '%'.$search.'%')
                      ->orWhere('app_jobtitles.title', 'like', '%'.$search.'%')
                      ->orWhere('app_technologies.title', 'like', '%'.$search.'%')
                      ->orWhere('app_members.summary', 'like', '%'.$search.'%');
            })
            ->get();

Note the following join in the above code, which is in your case category and sub category

请注意上面代码中的以下加入，这是在您的案例类别和子类别中

->leftJoin('app_members_jobtitles', 'app_members.id', '=', 'app_members_jobtitles.app_members_id')
        ->leftJoin('app_jobtitles', 'app_members_jobtitles.app_jobtitles_id', '=', 'app_jobtitles.id')