Using Count() in Laravel

Asked

Viewed 6,521 times

3

I have a controller who plays for my view the total number of courses registered in the portal. My doubt is due to the operation of the method count().

For example, the output of both instructions below are equivalent:

1) Instruction

$usuario = User::find(Auth::id());
$usuario->cursos->count();

2) Instruction

Curso::count('id', Auth::id());

My question is, in the instruction 1 I’m running a select fulltable, returning all table columns and then passing to the method count() count?

And the instruction 2, she already rides a sql that executes the count directly in the bank returning only the total?

php laravel laravel-eloquent

1 answer

4


My question is, in instruction 1 I am running a select fulltable, returning all table columns and then passing to the Count() method to perform the count?

In this first code is returned 1 user of the type User and with access to the cursos and then the method count() of classe Collection is counted how many items have in this collection. Actually here are 2 SQL, one retrieving the user and the other bringing the courses of that user (unnecessary reading). There’s a problem instead of you accessing the relationship, cursos() is accessed to course collection (reading, from table data) and this is very wrong, there is no need at this time, when to use the data simply do:

$usuario = User::find(Auth::id());
$usuario->cursos()->count();

continues to be 2 SQL, but at least does not load unnecessary table data.

And statement 2, it already mounts an sql that runs Count directly in the database returning only the total?

SQL is direct, only 1 SQL bringing the result per filter of the amount of courses, compared to 1 is direct and becomes better.

In short:

The second is more optimized and objective and in the first you take two turns, because, 

Auth::id()

could be:

Auth::user()->cursos()->count();

since it is understood that the count of courses and the user logged in (or authenticated). In this line Auth::user()->cursos()->count(); is created 1 SQL for course count as logged in user.

  • 1

    I understood, but by doing so "Auth::user()->courses()->Count();", eloquent mounts a Count sql, or returns all of that user’s data to later count?

  • 1

    @Fabio made some edits, and your doubt is in it, but, is 1 SQL that makes a filter as relationship with the user table and counts how many courses this user has, would be select count(*) from cursos where userid = 1, got it?

  • 1

    Perfect. One last thing, I get a dump to see sql?

  • 1

    Auth::user()->cursos()->toSql() for example quit SQL, I did other edits now I think it looks better, read item 1

  • Very good. I was only in doubt in one part, when seeing sql, we can observe that it is a normal select. When chaining Count(), select is changed to a Count at the bank level or this count is due to an application method?

  • 1

    @Fabius is executed to SQL of count by the call of the method count(), which can be done correctly by accessing the method cursos() in that case a SQL to the bank, other than cursos which is what you said in the second part that is already the return of all courses that user (being more expensive and bad in this aspect of your application, ie in this case), do two tests that is ideal for you to understand: Auth::user()->cursos() will return a Builder object (SQL construction summarizing) and Auth::user()->cursos will return a Collection class full of data.

  • 1

    Very good. I get it now. Thank you for sharing your knowledge Virgilio.

Show 2 more comments

Browser other questions tagged php laravel laravel-eloquent

You are not signed in. Login or sign up in order to post.