I would like to collect data inside a component. From a known course (course_id) I need the associated lessons, teachers and rooms. From the following working SQL query (MySql Workbench) I'm struggling building this inside the component:

select 
    habinho_school_lessons.time_from, 
    habinho_school_lessons.time_to, 
    habinho_school_teachers.first_name,
    habinho_school_teachers.name,
    habinho_school_rooms.name
from 
    `habinho_school_lessons` 
inner join 
    `habinho_school_teachers` on `teacher_id` = `habinho_school_teachers`.`id` 
inner join 
    `habinho_school_rooms` on `room_id` = `habinho_school_rooms`.`id` 
where 
    `course_id` = 2 and `is_activated` = true 
order by 
    `time_from` asc

My models:

Course.php:

public $hasMany = [
    'lessons' => [
        'Habinho\School\Models\Lesson',
    ],
    'lessons_count' => [
        'Habinho\School\Models\Lesson',
        'count' => true
    ],
];

Lesson.php:

public $belongsTo = [
    'courses' => ['Habinho\School\Models\Course',
        'table' => 'habinho_school_courses',
        'foreignKey' => 'course_id'],
    'teacher' => ['Habinho\School\Models\Teacher',
        'table' => 'habinho_school_teachers',
        'foreignKey' => 'teacher_id'],
    'room' => ['Habinho\School\Models\Room',
        'table' => 'habinho_school_rooms',
        'foreignKey' => 'room_id']
];

Room.php:

public $hasMany = [
    'lessons' => [
        'Habinho\School\Models\Lesson',
        'table' => 'habinho_school_lessons']
];

Teacher.php:

public $hasMany = [
    'lessons' => [
        'Habinho\School\Models\Lesson',
        'table' => 'habinho_school_lessons']
];

Last updated 8 years ago

Ok. I solved it with a different solution path.

In components / Course.php:

protected function loadLessons()
{
    $lessons = Lesson::getLessonsFromCourse($this->currentCourse['id']);
    $lessons->each(function($lesson) {
        $lesson->setTeacher($lesson->teacher_id);
        $lesson->setRoom($lesson->room_id);
    });
    return $lessons;
}

In model / Lesson.php:

public static function getLessonsFromCourse($course_id)
{
    return self::orderBy('time_from')->where('course_id', '=', $course_id)->get();
}

public function setTeacher($teacher_id)
{
    return $this->teacher = Teacher::select('first_name', 'name')->where('id', '=', $teacher_id)->first();
}

public function setRoom($room_id)
{
    return $this->room = Room::select('name')->where('id', '=', $room_id)->first();
}

Don't know this is the "official" way to solve this, but for me it works.

Last updated 8 years ago

I'm quite new to both Laravel and OctoberCms, but I have experienced knowledge with regards to ORMs in general. My approach would have been

Acme\Demo\Models\Lesson.php

public function scopeForCourse($query, $courseId) {
    return $query->where('course_id', '=', $courseId);
}

public function scopeActivated($query) {
    return $query->where('is_activated', '=', '1');
}

Acme\Demo\Components\List

public function getLessons() {
    return \Acme\Demo\Models\Lesson::forCourse($courseId) // This is the query scope method "Lesson::scopeForCourse()"
    ->activated() // This is the query scope method "Lesson::scopeActivated()"
    ->with('teachers', 'room')
    ->all();
}

This should get you all the needed lessons for the given course with their teachers and rooms assigned.

Some of the documentation on the ORM is taken directly from Laravel's docs at https://laravel.com/docs/5.2/eloquent-relationships#querying-relations

You could also go the other way and start off with your course model and query this with all the related info e.g.,

Acme\Demo\Components\List

public function getLessons() {
    return  \Acme\Demo\Models\Course::with([
        'lessons' => function($query) {
            $query
                ->orderBy('time_from', 'asc');
        },
        'teachers',
        'room',
    ])
    ->activated() // This is the query scope method "Lesson::scopeActivated()"
    ->all();
}

Last updated 8 years ago