Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

henninghorn's avatar

Any way to improve this Eloquent query?

I'm trying to pull available lectures for a user in my project. The setup consists of a few tables: Users, Courses and Lectures (+ course_user (pivot table))

When a user is logged in I want to pull the available lectures from the courses the user is enrolled in. I've done the following, but I'm not sure this is the most efficient way to the available lectures:

// Call from controller
$lectures = Lecture::availableNow($user)->get();

// Local scope in Lecture model
public function scopeAvailableNow($query, $user)
    {
        return $query->whereHas('course.students', function ($q) use ($user) {
            $q->where('id', $user->id);
        });
    }

The above generates the following SQL query:

select * from "lectures" where exists (select * from "courses" where "lectures"."course_id" = "courses"."id" and exists (select * from "users" inner join "course_user" on "users"."id" = "course_user"."user_id" where "course_user"."course_id" = "courses"."id" and "id" = ? and "course_user"."role" = ?))

Is this an efficient way to get the lectures? Or should I go another way around the models to get to that data?

I raw approach would probably look something like this:

SELECT l.name FROM lectures AS `l`
INNER JOIN courses AS `c` ON c.id = l.course_id
INNER JOIN course_user AS `cu` ON c.id = cu.course_id
WHERE cu.user_id = 3
0 likes
14 replies
thefuzzy0ne's avatar
thefuzzy0ne
9 years ago
Best Answer
Level 6

I'd set up a relation in your User model. You could probably use a HasManyThrough. Then you'd do something like this:

class User extends Model 
{

    // ...

    public function lectures()
    {
        return $this->hasManyThrough(\App\Lecture::class, \App\Course::class);
    }

    // ...
}

Then you should be able to do something like:

$user->lectures;

I think that's what you're after. However, please try not to get too caught up on efficiency unless you have a performance issue. Personally, I favour code that's logical, readable and easy to maintain.

2 likes
henninghorn's avatar

@thefuzzy0ne

Thanks for your reply!

Hadn't thought of that approach, thanks.

I definitely hear what you're saying regarding get too caught up with early "optimizations". It's a bad habit :)

thefuzzy0ne's avatar

No problem. We're all learning and I only have one very simple Laravel project under my belt at the moment. Working hard on number 2, which is MUCH more complicated. xD

A slight refactor to my code:

return $this->hasManyThrough(Lecture::class, Course::class);

The namespace was redundant. :)

henninghorn's avatar

@thefuzzy0ne

Cool! I've done a few, but I also keep learning a lot, especially when it comes to models and eloquent :)

I actually don't believe the hasManyThrough applies to my scenario. It's a many-to-many relation between courses and users. A course can have many users enrolled, and a user can be enrolled in many courses. And a lecture can only belong to one course, but a course can have many lectures.

The hasManyThrough relation generated this SQL:

select "lectures".*, "courses"."user_id" from "lectures" inner join "courses" on "courses"."id" = "lectures"."course_id" where "courses"."user_id" = 3
thefuzzy0ne's avatar

Correct me (again) if I'm wrong but that does apply. If a user has many courses and a course has many lectures, then even if a course only had a single lecture, by definition, if a user is enrolled in many courses, there will be many lectures.

However, you're absolutely right in that it's not doing the job I expected it to: I think I need to make it simpler.

User.php

public function courses()
{
    return $this->belongsToMany(Course::class);
}

public function lectures()
{
    return $this->courses()->lectures; // Might need to remove the curly braces
}

Courses.php

public function lectures() 
{
    return $this->hasMany(User::class);
}

Lecture.php

public function course()
{
    return $this->belongsTo(Course::class);
}

I think getting lectures() in the User model to work is the key and I know it's possible. Then you can still do $users->lectures to get the lectures.

I try to think of query scopes as being a filter for your query, not a means of getting related data. That's what relationships are for. Hope that makes sense.

henninghorn's avatar

@thefuzzy0ne

public function lectures()
{
    return $this->courses()->lectures; // Might need to remove the curly braces
}

Doesn't seem to be possible.

But this kind of works:

public function lectures()
    {
        return $this->courses()->with('lectures');
    }
thefuzzy0ne's avatar

Sorry, by curly braces I actually means brackets. xD

return $this->courses->lectures;

However, I think that'll actually result in two queries but I doubt that any fluffy kittens will be harmed as a result. :)

I'd also give this a go:

return $this->courses()->lectures()->get();

That should only be one query as I think (if it works as I hope), we're just traversing the relationships.

Sometimes you've just gotta throw shit at the wall and see what sticks. That's always been a valuable learning technique for me, anyway.

henninghorn's avatar

@thefuzzy0ne

Okay, if I do this from my controller:

return $user->lectures()->get();

While having this in my User.php

public function lectures()
    {
        return $this->courses->lectures;
    }

I get this error:

ErrorException in User.php line 52:
Undefined property: Illuminate\Database\Eloquent\Collection::$lectures

If I change User.php to:

public function lectures()
    {
        return $this->courses()->lectures();
    }

I get this error:

BadMethodCallException in Builder.php line 2345:
Call to undefined method Illuminate\Database\Query\Builder::lectures()

Perhaps it's not possible to do it this way?

thefuzzy0ne's avatar

Using this:

public function lectures()
{
    return $this->courses()->lectures(); // Only return a relationship
}

You should be able to just do this from your controller:

$lectures = $user->lectures; // This should query that relationship through a magic method.

As I said, I know it's possible, and if I has more time, I'd test it all out for you and give you a working example instead of guessing all the time. xD

If you don't mind two queries, just go with $user->courses->lectures;

What took me a while to get to grips with when I first started learning Eloquent, is that:

$user = User::find(1);
$relationship = $user->courses(); // Returns a relationship (which is also an instance of the query builder).
$courses      = $user->courses; // Performs the query on the relationship and returns the results (a model or a collection of models).
thefuzzy0ne's avatar

I don't suppose you'd be willing to share your migrations and perhaps a model factory or seed file to get a test database up and running? If so, I can look at it tomorrow.

Rather than continuing to baffle you with guessing, I'd very much prefer to enlighten you with a working example.

thefuzzy0ne's avatar

@henninghorn Thanks for this. Tried firing up my Vagrant box - BSOD! Located the fault and rolled back the faulty driver - error when running Vagrant. Found a file that had become corrupt and fixed it. Now firing up Vagrant...

I should be back shortly. :)

thefuzzy0ne's avatar

@henninghorn Well, I thought this was possible, (and it still might be), but it's not as simple as I expected. Using the relationships you currently have in place, you should be able to simply do this from your controller:

User::with('courses.lectures')->find($userId);

This will eager load the courses and lectures data and return an array of courses, which you can loop through to obtain the lectures. Hopefully, this'll do the trick.

henninghorn's avatar

@thefuzzy0ne

Thanks for taking the time to test it out! Really appreciate it. I've been inspired to some refactoring ideas by this :)

Please or to participate in this conversation.