phpmysqllaravelintersection

check if all elements of an array exists in a table in laravel

I have courses and student_courses table. If a student completed a course I'm inserting a row in student_courses table with student_id and course_id. Now I have a scenario where I need to get all student_ids from student_courses table if a student completed certain numbers of courses. I am getting course_ids from another query as an array. Is there any way to make this query efficiently in laravel, because there is already 100k+ data in student_courses table?

Solution

  • Don't know using query, but you can do that using array_intersect() function.

    // get all courses id you want to check into one array
$checkCourses = [1,2,...];

// get all courses completed by student
$completedCourses = StudentCourse::where('student_id', $studentId)->pluck('course_id');

    now you can use array_intersect()

    $result = array_intersect($completedCourses, $checkCourses);
if(!empty($result)) {
    // your code...
}

    following query will gives the student who completed course id (1,3,4)

    DB::query("SELECT student_id FROM student_courses WHERE course_id IN (1,3,4) GROUP BY student_id HAVING COUNT(*) = 3");

    where count 3 is size of total course you want to check, in above query we are checking with course id 1,3,4 so HAVING COUNT(*) is 3