Laravel链接范围查询

Question

I have two scope queries for my DB.

The data I query looks like this:

   {
       year: "2015",
       term: "Summer",
       subject_code: "DIGM",
       course_no: "350",
       instr_type: "Lab",
       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: ""
   }

search simply looks for items based on user input, so like the name of the course, or its instructors

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

The following scope queries returns simply the lectures. I tried this query like this: $class->lectures()->get() and it works - it pulls all the lectures.

public function scopeLectures($query) {
    return $query
        ->where('instr_type', 'like', LECTURE) // where LECTURE is a constant
        ;
}

However, if I chain my scope queries together:

$class::search('digm 350')->lecture()->get();

I would get all the results of search instead of search->lab.

Not quite sure why.

Answer 1

The reason is the ORs that you do in search. When you apply both scopes the query that is generated is:

where `course_title` like ? or subject_code || ' ' ||  course_no like ? or `instructor` like ? and `instr_type` like ?

while what you need is

where (`course_title` like ? or subject_code || ' ' ||  course_no like ? or `instructor` like ? and `instr_type` like ?)

Notice that the constraints from first scope have been put in parentheses.

You need to change the way you apply constraints in your search scope:

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

}