Optimize Query

I have The following:

Users/Students
Events
Courses

I want to retrieve the students count for each course.

Course model has a relationship with events (polymorphic)

public function events()
    {
        return $this->morphToMany('App\Models\Event', 'schedulable');
    }

Event model has relationship with students/users

public function students()
    {
        return $this->belongsToMany('App\Models\User');
    }

I can get the information I want using the following method on the course model

public function getStudentsCount()
    {
        $count = 0;

        foreach($this->events as $event) {
            $count += intval($event->students->count());
        }

        return $count;
    }

I can access the data using

$course->getStudentsCount() // returns -> 5017

How can I make this more efficient when I'm listing out the courses and want to order by "popular"

Any tips would be greatly appreciated.

Thanks

Tray2

5 years ago

Level 73

You really need to do that in the database. It's way more efficient.

CorvS

5 years ago

Level 27

@adgower IMO adding a hasManyThrough relationship to your Course model is the easiest way. Then you can do

$studentCount = $course->students()->count();

or if you want the count for each course and order it by popularity

Course::withCount('students')->orderBy('students_count', 'desc')->get();

You'd have to consider that the hasManyThrough relationship is over a polymorphic relationship tho.

1 like

Adgower

5 years ago

Best Answer

Level 14

Hey I went this route using package has many deep thanks

use \Staudenmeir\EloquentHasManyDeep\HasRelationships;
    public function eventStudents()
    {
        return $this->hasManyDeepFromRelations($this->events(), (new Event)->students());
    }

In my query I use

withCount(‘eventStudents’)

I can also easily sort by the count

Please or to participate in this conversation.