Relationship count in Laravel 4

Asked

Viewed 109 times

1

In Laravel 4, we do not have the method present in Laravel 5.2 >= called withCount. That one withCount is responsible for adding to SELECT the counting of items related to the current entity.

For example:

  $u = Usuario::withCount('posts')->first();
  var_dump($u->posts_count); // int(5)

But in my case, I am maintaining a system that I did some 3 years ago, where I still used the Laravel 4. And the solution that we had at that time, without writing SQL in hand, was to call a count for each related item.

Thus:

 foreach($usuarios as $usuario) {
      echo $usuario->posts()->count();
 }

The problem with that is that for every iteration, Laravel will call a SELECT COUNT(*) FROM posts WHERE usuario_id = ?, and I imagine that in a 500-line iteration, it will screw up my database server.

And in my case, I also believe it is impossible to migrate the project from Laravel 4 to 5.

So I ask you:

  • How can I load in advance (Eager Loader) the count of items related to an entity (model) in Laravel 4?
laravel laravel-4

  • Do you need to load this counter from all users on a given page? Otherwise you could page and load X results with each interaction. In case you need to load everything, which?

  • @Darleifernandozillmer create an attribute to save in white violates the rules of normalization. I would like a solution close to the withCount even

  • This you can solve with eagerload. Usuario::with('posts')->get();

  • @cau not. That brings up all the records, and when I had 10,000 records in the relationship screwed up my application (not to mention that doing so is a no-size gambiarra)

  • This worked in Laravel 5. I haven’t tried in 4. $users = User::Join('posts', 'posts.user_id', '=', 'users.id')->groupBy('users.id')->get(['users.id', 'users.name', DB::raw('Count(posts.id) as posts')]); Jeffrey Way: https://laracasts.com/discuss/channels/eloquent/eloquent-query-with-count-of-objects-in-a-one-to-many-relationship

1 answer

0


To be registered here on the site: to solve this situation, I had to use a somewhat astonishing idea. I did an almost manual implementation using some features present in the class Illuminate\Database\Query\Builder and Illuminate\Database\Eloquent\Builder.

In my case, I had the model Remessa and each consignment has x requests. I would need something similar to the code below if I were on Laravel 5.

 Remessa::withCount('solicitacoes')->get()

In case, to do this, I created a scope in the model Remessa that generated the subquery I passed to the SELECT of Remessa, thus:

public function scopeQuantidadeSolicitacoes($query, $as = 'solicitacoes_quantidade', Closure $callback = null)
{
    $subQuery = Solicitacao::whereRaw('remessas.id = solicitacoes.remessa_id')->selectRaw('COUNT(*)');

    $callback && $callback($subQuery);

    $subSelectExpression = DB::raw(sprintf('(%s) as %s', $subQuery->toSql(), $as));

    // Caso não exista um `SELECT` especificado, coloca * por padrão
    if ($query->getQuery()->columns === null)
    {
        $query->select('*');
    }

    $query->addBinding($subQuery->getQuery()->getBindings(), 'select');

    return $query->addSelect($subSelectExpression);
}

To use just do so:

Remessa::quantidadeSolicitacoes('qtd_solicitacoes')
        ->quantidadeSolicitacoes('qtd_solicitacoes_bloqueadas', function ($query) {
            $query->where('bloqueado', '=', 1);
        })
        ->get();

The result is practically the same as the Laravel 5.

Browser other questions tagged laravel laravel-4

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