如何将此内连接mysql查询转换为Laravel的流畅？

Question

I have this inner join query that I want to covert to Laravel's fluent. The things it is partially working. I'm able to get the results but those operators that mysql supports like and I'm finding it difficult to apply it to my fluent query.

mysql query:

SELECT students.surname as Name, subjects.name as Subject, grades.name as Class, terms.name as Term, score as Score

from scores
    inner join students
        on students.id = scores.student_id
        and scores.student_id = 1
    inner join subjects
        on subjects.id = scores.subject_id
        and scores.student_id = 1
    inner join grades
        on grades.id = scores.grade_id
        and scores.student_id = 1
    inner join terms 
        on terms.id = scores.term_id
        and scores.student_id = 1

where scores.term_id = 1 or scores.term_id = 2 or scores.term_id = 3;

laravel query:

$scores = \DB::table('scores')
        ->join('students', 'students.id', '=', 'scores.student_id')
        ->join('subjects', 'subjects.id', '=', 'scores.subject_id')
        ->join('grades', 'grades.id', '=', 'scores.grade_id')
        ->join('terms', 'terms.id', '=', 'scores.term_id')
        ->select('students.surname', 'subjects.name', 'grades.name', 'terms.name', 'score')
        ->where('students.id', '=', '1', 'and', 'scores.term_id', '=', '1', 'or', 'scores.term_id', '=', '2', 'or', 'scores.term_id', '=', '3')
        ->get();

The problem I now have is in my where clause. It's seems like the and operator is being overlook and returning results that are not to be in the result set.

this is the result set when I dd it:

4th Period shouldn't be in the result set as it is term 4. Note term 1 is 1st Period, term 2 is 2nd Period and term 3 is 3rd Period

Answer 1

Check out https://laravel.com/docs/5.4/queries#where-clauses for more documentation but you can clean up that where clause you made (you can change the documentation to represent the version of laravel you are using in the upper right hand corner).

instead of ->where('students.id', '=', '1', 'and', 'scores.term_id', '=', '1', 'or', 'scores.term_id', '=', '2', 'or', 'scores.term_id', '=', '3')

Do:

->where('students.id', 1)
->where('scores.term_id', 1)
->orWhere('scores.term_id', 2)
->orWhere('scores.term_id', 3)