Relationship counting in Laravel 4

1

In Laravel 4, we do not have the method present in Laravel 5.2 > = called withCount . This withCount is responsible for adding to the SELECT the count 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'm maintaining a system I did some 3 years ago, where I still used Laravel 4. And the solution we had at the time, without writing SQL in hand, was to call a count for each related item.

So:

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

The problem with this is that, for each iteration, Laravel will call a SELECT COUNT(*) FROM posts WHERE usuario_id = ? , and I imagine that in an iteration of 500 rows, this will screw up my database server.

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

So, I ask:

  • How can I load (eager loader) the count of items related to an entity (model) in Laravel 4?
asked by anonymous 09.08.2018 / 14:22

1 answer

0

To be registered here on the site: to solve this situation, I had to use a rather flashy idea. I've done an almost manual implementation using some features present in the Illuminate\Database\Query\Builder and Illuminate\Database\Eloquent\Builder classes.

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

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

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

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 this:

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

The result is almost the same as Laravel 5.

    
09.08.2018 / 18:12