'Where like'子句，雄辩地使用2列的连接值 [英] 'Where like' clause using the concatenated value of 2 columns with eloquent

问题描述

我有一个查询，它在几列中搜索一个词，其中之一必须是全名.

I have a query that searches for a term in several columns and one of them has to be a full name.

我已经用名字和姓氏分隔了全名，因此在搜索时我必须合并这两个值.

I have separated the full name in name and lastname, so I have to concat these 2 values when searching.

我现在只搜索姓名.我如何将concat添加到姓氏?我正在调查变异者，但我不知道这是否是方法.

I have this right now only searching for name. How I would add the concat to lastname? I was investigating over mutators but I don't know if it is the way to way.

public function search($searchQuery)
{
    $search = "%{$searchQuery}%";

    return Order::with('customer')
                    ->orWhereHas('customer', function($query) use ($search){
                        $query->where('dni', 'like', $search);
                    })
                    ->orWhereHas('customer', function($query) use ($search){
                        $query->where('name', 'like', $search);
                    })
                    ->orWhere('code', 'like', $search)
                    ->paginate(15);
}

推荐答案

Mutators并不是解决问题的方法，因为它们不会影响正在构建的查询，它们仅用于存储在模型中的值.但是，您可以使用以下条件:

Mutators are not the way to go here, because they do not affect the query you're building, they are only used on the values stored in the model. However you can use the following condition:

$query->where(DB::raw('CONCAT_WS(" ", name, lastname)'), 'like', $search);

MySQL函数 CONCAT_WS 将连接两个字符串，并将它们与作为第一个参数传递的字符串粘合在一起.因此，在这种情况下，如果名字是John而姓氏是Smith，则条件将被评估为此"John Smith" like "%query%". DB:raw用于避免条件的第一部分被逃逸，这会使连接语句周围出现反引号.

The MySQL function CONCAT_WS will concatenate two strings, gluing them with the string passed as the first parameter. So in this case if the first name is John and the last name is Smith, the condition will be evaluated to this "John Smith" like "%query%". The DB:raw is used to avoid the first part of the condition to be escaped which would put backticks around the concatenation statement.

这篇关于'Where like'子句，雄辩地使用2列的连接值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！