Laravel chaining scope queries

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.

jedrzej.kurylo · Accepted Answer · 2015-07-16 23:31:04Z

3

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 . '%');
 });

}

answered Jul 16, 2015 at 23:31

jedrzej.kurylo

41.1k10 gold badges108 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

theGreenCabbage Over a year ago

Ah.. I see what you did there. Thank you.

theGreenCabbage Over a year ago

This worked perfectly. I didn't think of the query that way. Is it possible for Eloquent to print out the raw SQL query so we can examine it the way you did?

jedrzej.kurylo Over a year ago

You need to call toSql() on query object, e.g. Model::search('string)->lectures()->toSql();

Mhluzi Bhaka Over a year ago

If you don't have it already try the github.com/barryvdh/laravel-debugbar to display the sql query including parameters - I have found it very helpful.

Collectives™ on Stack Overflow

Laravel chaining scope queries

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related