ctyler
5 months ago

Stored Procedure always return an empty array

Posted 5 months ago by ctyler

Good After noon all. I am having an issue running a stored procedure in a Controller: Laravel 5.8 Mariadb: 10.4.8

Here is the stored procedure:

DELIMITER $$
CREATE DEFINER=`dbuser`@`10.6.12.18` PROCEDURE `GetStudentsToGrade`(
    IN course_ID INT,
    IN assignmentID INT
)
BEGIN
    SELECT c.id as courseID, c.name, count(sess.id) as TotalSessions, y.userID, y.firstname, y.lastname, y.NoAttended, y.SessionID, y.updated_at, z.grade, z.updated_at
FROM courses c
JOIN attendances a on c.id = a.course_id
left join attendancesessions sess on a.id = sess.attendance_id
LEFT JOIN
(
                SELECT c.id as courseID, u.id as userID, u.firstname, u.lastname, sess.id as SessionID, count(al.status) as NoAttended, al.updated_at
                FROM attendancelogs as al
                join attendancesessions sess on al.attendancesession_id = sess.id
                join attendances a on sess.attendance_id = a.id
                join courses c on a.course_id = c.id
                join users u on al.user_id = u.id
                WHERE c.id = @courseID
                AND al.status = 1
    GROUP BY userID, courseID
) as y on c.id = y.courseID
LEFT JOIN
(
                SELECT users.id, users.firstname, users.lastname, assign_grades.grade, assign_grades.updated_at, assign_grades.assignment_id
                FROM assign_grades
                LEFT JOIN users on assign_grades.user_id = users.id
                JOIN assignments on assign_grades.assignment_id = assignments.id
                WHERE assignments.id = @assignmentID
) as z on y.userID = z.id
WHERE courseID = @courseID
GROUP BY y.userID, c.id
HAVING  TotalSessions = y.NoAttended;
END$$
DELIMITER ;

If I run this in MySQL Workbench I get the result I expect.

CALL GetStudentsToGrade(5, 97);

Now in the controller:

$studentsToGrade = DB::select(DB::raw("call GetStudentsToGrade('$course->id', '$assignment->id')"));

dd($studentsToGrade, $course->id, $assignment->id);

The dump show:

[]

5

97

I also tried:

$studentsToGrade = DB::select(DB::raw("call GetStudentsToGrade(5, 97)"));

With the same results. I cannot seem to figure out why this is not working,

Any help would be greatly appreciated.

Please sign in or create an account to participate in this conversation.