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.

I seem that you are sorting your result with PHP, why don't you use MYSQL for this ? — Mcsky, Aug 30 '17 at 08:46
Because I dont know how to use MYSQL to order by this field. — Juan Lopez, Aug 30 '17 at 09:01
I don't use Laravel, but you should read the documentation to perform an order by with mysql. This will be faster and avoid the php memory limit error. — Mcsky, Aug 30 '17 at 09:04
Thanks @Mcsky. I know how to use orderBy in mysql but I dont know how to use it with this specific field, because it comes from a model relationship. Anyway I get the error even before de ordering start executing. — Juan Lopez, Aug 30 '17 at 09:13
it's better to use ordering with eloquent and not with php! will rise performance issues, check the answer below — Sletheren, Aug 30 '17 at 09:21

Sletheren · Accepted Answer · 2017-08-30T10:15:50.543

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();

edited Aug 30 '17 at 10:15

answered Aug 30 '17 at 09:20

Sletheren

2,435
11
25

But how can I put the log creation date inside the orderBy? This is not a column of poster table. – Juan Lopez Aug 30 '17 at 09:32
when you're inside the callback, you can access the pos_log_resumenEnviado directly, just put orderBy('created_at','ASC'); – Sletheren Aug 30 '17 at 09:34
So many thanks. Now I can use paginate() and avoid the php memory error. Unfortunatly now the order of items is not correct. – Juan Lopez Aug 30 '17 at 09:53
if you check the pos_log_resumenEnviado for each poster, are they order correctly? – Sletheren Aug 30 '17 at 10:02
I realized the problem is that "$q->orderBy('created_at','ASC');" is ordering by the 'created_at' of poster, instead of created_at of log – Juan Lopez Aug 30 '17 at 10:03
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/153226/discussion-between-juan-lopez-and-sletheren). – Juan Lopez Aug 30 '17 at 10:06

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

1 Answers1