How can I improve my query for not to get "PHP Fatal error: Allowed memory size" (Ordering and paginating in laravel 5.2)

Question

I have a several items called posters that have logs. I want to order that posters by the date creation of these logs.

I have a model called Poster with this code:

class Poster extends Model {

protected $table = 'posters';

public function pos_log_resumenEnviado()
{
    return $this->hasMany('App\models\PosterLog', 'pos_id')->where('log', 'Autores de poster enviado')->orWhere('log', 'Resumen de poster modificado')->latest();
}

Then in my controller I retrieve results like this:

$posters = Poster::with('pos_log_resumenEnviado', 'user')->where('state', 'Resumen pendiente de aceptacion')->get();

$posters = $posters->sortBy(function($pos)
{
    if( sizeof($pos->pos_log_resumenEnviado) > 0)
        return $pos->pos_log_resumenEnviado[0]->created_at;
})->take(1200);

I get all posters and then I order them and take the first 1200 results. This has been working fine (with a slow performance) but now I get the php error I said in the title. Error comes even before the sortBy execution, just with the get()

If I did not have to use sortBy I could use paginate() method but I dont know if there is another way to order that.

Answer 1

Here is the correct query you have to use First, use where first to exclude the unwanted entries and then inside the with callback you can filter the results using eloquent :

$posters = Poster::where('state', 'Resumen pendiente de aceptacion')->with(['pos_log_resumenEnviado' => function($q){
                return $q->orderBy('poster_logs.created_at','ASC');
            }])->with('user')->get();