Querying collections in Laravel

Question

Have I got it correctly that when I query a Laravel collection, it doesn't query the database but executes the query on what was already fetched?

For example, I have a relation that returns a collection:

public function permissions()
{
    return $this->belongsToMany(Permission::class, RolePermission::getModelTable(), 'role_id', 'permission_id');
}

Does the following code query the database or it works with the collection using php facilities?

$role->permissions->where('code','global.test')->count()

And, as far as I understand, if I query the relationship then the database will be queried instead of working with the results that were already fetched:

$role->permissions()->where('code','global.test')->count()

So basically, $role->permissions - working with the fetched results "offline", but $role->permissions() - querying the database

What way is generally more efficient and when?

Answer 1

You're basically right. The difference between calling $role->permissions and $role->permissions() is that the first returns an instance of Collection, while the second returns an instance of BelongsToMany.

Collection is a collection (really?) of related objects and BelongsToMany is the relation itself. So yes, by calling the method (and not the magic property) you are querying the database.

Update

I didn't get the last question, sorry. The first time you call $role->permissions (magic property), Laravel fetches all of the permissions associated with $role, if they were not eager loaded. If you need only a subset of those permissions, you can filter them by using any of the magic property and the method. Let me do some examples.

$role = Role::first();
// Fetch all the permissions and count a subset.
$role->permissions->where('code', 'global.test')->count();
// Count another subset.
$role->permissions->where('code', 'another.test')->count();

The same can be done using the method:

$role = Role::first();
// Fetch a subset of the permissions and count it.
$role->permissions()->where('code', 'global.test')->count();
// Fetch another subset and count it.
$role->permissions()->where('code', 'another.test')->count();

As you can see, in the first example you make only one query and filter the results differently. In the second example you make two queries. The first one is obviously more efficient.

If you need only a subset during the same execution, though, things change. Here we are using eager loading:

$role = Role::with('permissions', function($query) {
    // Here we filter the related permissions.
    $query->where('code', 'global.test');
})->first();
// We have what we want. No need to filter the collection.
$role->permissions->count();
// Let's do something else with this subset.
$role->permissions->all();

What if you fetch all the related objects, but need only that subset?

$role = Role::first();
// Filter the collection to count the needed subset.
$role->permissions->where('code', 'global.test')->count();
// Filter the collection to get the needed subset.
$role->permissions->where('code', 'global.test')->all();

As you can see, in the second example we are much less DRY, and we are also doing the same operation multiple times. Less efficient, of course.