Laravel模型中多列数据的搜索范围 [英] Search scope for multiple columns of data in Laravel Model

问题描述

我在Laravel中有一个名为Blah的模型(之所以这样命名，是因为您不能简单地为类Class命名，所以我暂时更改了名称).

I have a Model within Laravel called Blah (I named it this way because you can't simply name a class Class, so I changed the name temporarily).

在classes表中，每一行包含如下数据:

Within the classes table, each row contains data like this:

   {
       year: "2015",
       term: "Summer",
       subject_code: "DIGM",
       course_no: "350",
       instr_type: "Lecture",
       instr_method: "Face To Face",
       section: "003",
       crn: "42953",
       course_title: "Digital Storytelling",
       credits: "3.0",
       day: "R",
       time: "06:30 pm - 09:20 pm",
       instructor: "Teacher Name",
       campus: "University Building",
       max_enroll: "18",
       enroll: "18",
       building: "PLACE",
       room: null,
       description: "By surfing the internet and playing computer games, by lectures, assigned readings, class screening, and research projects, this class explores the impact of digital media on art, design and daily living. This is a writing intensive course. ",
       pre_reqs: "",
       co_reqs: ""
   }

我在Blah模型中开发了范围搜索查询，如下所示:

I developed a scope search query within my Blah Model like so:

/**
 * Search scope query
 *
 * @param string
 * @return QueryBuilder
 */
public function scopeSearch($query, $searchTerm) {
    return $query
        ->where('course_title', 'like', "%" . $searchTerm . "%");
}

上面的作用域搜索将允许我搜索术语digital，它将返回名为Digital Storytelling的类.

The above scope search would allow me to search for the term digital and it would return the class called Digital Storytelling.

但是，我想进一步介绍一下-我希望能够在相同的搜索范围内执行DIGM 350之类的搜索(这两个数据点来自subject_code和course_no)，并且它会带来相同的结果.

However, I would like to take it further - I would like to be able to, in the same search scope, perform a search like DIGM 350 (these two data points are from subject_code and course_no) and it would bring up the same result.

因此，搜索DIGM 350和/或digital将显示相同的结果.这将是我网站的自动搜索框的一部分.

So, searching DIGM 350 and/or digital would bring up the same result. This would be part of my site's auto-search box.

最终查询:

/**
 * Search for course title or subject name
 * @param $query
 * @param $searchTerm Course Title or Subject Name
 * @return mixed
 */
public function scopeSearch($query, $searchTerm) {
    return $query
        ->where('course_title', 'like', '%' . $searchTerm . '%')
        ->orWhere(DB::raw("subject_code || ' ' ||  course_no"),
            'like',
            '%' . $searchTerm . '%'
        )
        ;
}

感谢伊恩.

推荐答案

您可以使用 orWhere()方法可以执行与您的示例类似的操作:

You could use the orWhere() method of the query builder to do something similar to your example:

    public function scopeSearch($query, $searchTerm) {
        return $query
            ->where('course_title', 'like', "%" . $searchTerm . "%")
            ->orWhere('subject_code', 'like', "%" . $searchTerm . "%")
            ->orWhere('course_no', 'like', "%" . $searchTerm . "%");
    }

但是，这里的真正问题是MySQL"like"查询可能没有强大功能来执行您要尝试执行的操作，因为subject_code或course_no列都不包含字符串"DIGM 350".您可以对类似以下内容的列进行类似查询:

However, the real problem here is that MySQL "like" queries may not be powerful enough to do what you're trying to do, since neither the subject_code or course_no columns contain the string "DIGM 350". You could do a like query on the concatination of those columns that would look something like this:

    public function scopeSearch($query, $searchTerm) {
        return $query
            ->where('course_title', 'like', "%" . $searchTerm . "%")
            ->orWhere(DB::raw("CONCAT_WS(' ',subject_code,course_no)"), 'like', "%" . $searchTerm . "%");
    }

这将执行类似的查询，以匹配其中以空格分隔的subject_code和course_no值包含搜索字符串(或如果course_title包含搜索字符串)的任何行.

That would do a like query matching any rows where the value of subject_code and course_no separated by a space contains the search string (or if the course_title contains it).

不过，我认为您的需求可能需要一个实际的搜索引擎.

I think your needs may warrant an actual search engine, though.

这篇关于Laravel模型中多列数据的搜索范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！