Question 16730 in Laravel Question posted in
The official Laravel Documentation can be found here.

Sorting data based on JSON content with Laravel

I have a table in my database that stores a student’s progress in a course.

        Schema::create('course_student', function (Blueprint $table) {
            $table->primary(['course_id', 'user_id']);
            $table->char('user_id');
            $table->char('course_id');
            $table->timestamp('lesson_timestamp')->nullable();
            $table->text('course_progress')->nullable();
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->foreign('course_id')->references('id')->on('courses')->onDelete('cascade');
            $table->timestamps();
        });

Course progress is stored as a JSON object and consists of several JSON objects with progress for a particular lesson.

{
  "6e980e75-72a6-4260-bbe6-7a764b4e7ce7" : {
    "tools" : 5,
    "exam" : 15,
    "intro" : 5,
    "dictionary" : 5,
    "class" : 35.555555555555557
  },
  "9827d702-c1a1-4fcc-a134-69385ccb7dde" : {
    "intro" : 5,
    "class" : 13,
    "exam" : 15
  },
  "87fd10e3-8140-448e-bf35-64097eb8be48" : {
    "tools" : 5,
    "exam" : 15,
    "intro" : 5,
    "dictionary" : 5,
    "class" : 40
  },
  "52700dd0-caaa-48e7-86f8-d50389fb915f" : {
    "tools" : 5,
    "exam" : 15,
    "intro" : 5,
    "dictionary" : 5,
    "class" : 40
  },
  "daae12a4-b1ac-4138-bbf3-423c3148fc57" : {
    "tools" : 5,
    "exam" : 15,
    "intro" : 5,
    "dictionary" : 5,
    "class" : 0
  }
}

In the process of studying the course, the student updates the date lesson_timestamp and the data in progress for a particular lesson.

If I sort users by lesson_timestamp, then everything works fine for me.

public function getUsers()
{
    return $users = User::query()
      ->select('users.id', 'users.email', 'users.lastSeen', 'users.totalTime', 'users.retention')
      ->orderBy(CourseStudent::select('lesson_timestamp')
                            ->whereColumn('user_id', 'users.id')
                            ->orderByRaw('lesson_timestamp is null')
                            ->oldest()
                            ->take(1));
      ->get();
}

Is it possible to sort users based on the calculation of the maximum / minimum number of keys class > 35 inside the course progress using the same approach?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Finaly I solved it with stored procedure on MySQL side

    CREATE DEFINER=`root`@`localhost` FUNCTION `calculateLessons`(input_data JSON) RETURNS int
    DETERMINISTIC
BEGIN
    DECLARE result INT DEFAULT 0;
    DECLARE lesson_key VARCHAR(50);
    DECLARE indx INT DEFAULT 0;

    SET @amount = JSON_LENGTH(JSON_KEYS(input_data));
    
    WHILE indx < @amount DO
        SET lesson_key = JSON_EXTRACT(JSON_KEYS(input_data), CONCAT('$[',indx,']'));
        IF JSON_EXTRACT(JSON_EXTRACT(input_data, CONCAT('$.',lesson_key)), '$.class') >= 39 || JSON_EXTRACT(JSON_EXTRACT(input_data, CONCAT('$.',lesson_key)), '$.practice') >= 30 THEN
            SET result = result + 1;
        END IF;
        SET indx = indx + 1;

    END WHILE;
return result;   
END

    And after I call it inside of my controller

        public function scopeOrderByLessons($query, $direction)
    {
      if ($direction === 'asc') {
        return $query->select('users.id', 'users.email')
                    ->join('course_student', 'course_student.user_id', '=', 'users.id')
                    ->groupBy('users.id')
                    ->orderByRaw('sum(calculateLessons(course_student.course_progress)) asc');
      } else {
        return $query->select('users.id', 'users.email')
                    ->join('course_student', 'course_student.user_id', '=', 'users.id')
                    ->groupBy('users.id')
                    ->orderByRaw('sum(calculateLessons(course_student.course_progress)) desc');
      }
    }

    It cost me time, so may be later I will find some other decisions


  2. 0

    Adding whereRaw statement to your query like:

    $users = DB::table('course_student')
    ->whereRaw('course_progress->"$.class" > 35')
    ->get();

    Should do your work

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search