How can I filter a negative value from a query made with eloquent and then paginate it?

Question

I was trying to get the remaining time for movie delivery in a rental system made with laravel. I built the following query using eloquent and it returned the result I wanted:

        $resting_time = DB::table('alquiler')
                    ->join('socio','alquiler.soc_id','=','socio.id')
                    ->join('pelicula','alquiler.pel_id','=','pelicula.id')
                    ->select('socio.soc_nombre','pelicula.pel_nombre','alquiler.created_at', DB::raw("DATEDIFF(alq_fecha_hasta,NOW()) AS Days"))
                    ->orderBy('Days','asc')
                    ->paginate(6);

but there is a problem when these rentals go over the delivery deadline it returns negative values, so I would like the query to return only the rentals that have the remaining days greater than zero and then paginate those results.

I create this statement and using map() filter only the positives that are returned in a collection but the problem is that I can't paginate them.

$resting_time = DB::table('alquiler')
                    ->join('socio','alquiler.soc_id','=','socio.id')
                    ->join('pelicula','alquiler.pel_id','=','pelicula.id')
                    ->select('socio.soc_nombre','pelicula.pel_nombre','alquiler.created_at', DB::raw("DATEDIFF(alq_fecha_hasta,NOW()) AS Days"))
                    ->get()->map(function($alquiler){
                        return ($alquiler->Days >= 0) ? $alquiler : null;
                    });
$resting_time = $resting_time->filter()->sortBy('Days');

This is the returning collection:

But this type of collection cannot be paginated.

Any idea how to fix it, or maybe an easier way to do it? Sorry if something doesn't make sense, I'm just starting in laravel.

Try `having()` method. refer [this](https://stackoverflow.com/a/49778504/6274211). Note from the comments, `use simplePaginate()` when using `having()` — iamab.in, Jul 10 '22 at 20:11

score 1 · Answer 1 · answered Jul 10 '22 at 20:08

In second case its not working,because you work with:

\Illuminate\Support\Collection::class

in first case, you work with :

\Illuminate\Database\Eloquent\Collection::class

To make it work , you can try to do next thing: take a

\Illuminate\Support\Collection::class

and return it paginated via

Illuminate\Pagination\Paginator::class

so the end result will look like this:

$resting_time = DB::table('alquiler')
        ->join('socio','alquiler.soc_id','=','socio.id')
        ->join('pelicula','alquiler.pel_id','=','pelicula.id')
        ->select('socio.soc_nombre','pelicula.pel_nombre','alquiler.created_at', DB::raw("DATEDIFF(alq_fecha_hasta,NOW()) AS Days"))
        ->get()->map(function($alquiler){
            return ($alquiler->Days >= 0) ? $alquiler : null;
        });
    $resting_time = $resting_time->filter()->sortBy('Days');

    return new Illuminate\Pagination\Paginator($resting_time, 6);

However, i would recommend to prepare data from SQL side, neither doing all of the manipulations from collection perspective.

Thanks for the recommendation and the answer, it worked partially if it returned the correct type of collection but I don't know why it didn't paginate all the elements, it only paginated the first ones. — tellxmaster, Jul 11 '22 at 01:17

Serghei Leonenco · Answer 2 · 2022-07-10T20:27:45.340

Most of the answers already provided will work, but will return a collection instead of a paginated resource. The trick is to use the tap helper method before map'ping, to return the same object you modified.

return tap(Alquiler::select(['socio.soc_nombre','pelicula.pel_nombre','alquiler.created_at', DB::raw("DATEDIFF(alq_fecha_hasta,NOW()) AS Days")])
            ->with('socio', 'pelicula')
            ->paginate(20))
            ->map(function ($model) {
                return ($model->Days >= 0) ? $model : null;
            });

or you can do this way too:

return Alquiler::select(['socio.soc_nombre','pelicula.pel_nombre','alquiler.created_at', DB::raw("DATEDIFF(alq_fecha_hasta,NOW()) AS Days")])
   ->with('socio', 'pelicula')
   ->paginate(20))
   ->map(function ($model) {
        if($alquiler->Days >= 0) {
            return $model;
        }
   });

I tried both ways but I got an error when calling the tap because it said that it sent too few arguments and in the other one despite using the with it said that it did not find the columns of the select, thanks anyway. — tellxmaster, Jul 11 '22 at 01:21

score 0 · Answer 3 · answered Jul 11 '22 at 01:14

I tried both methods and it didn't work at least the way I wanted, so I did a little more research and put this together:

    public function getRestingTime(){
    $resting_time = Alquiler::select(['socio.soc_nombre','pelicula.pel_nombre','alquiler.created_at', DB::raw("DATEDIFF(alq_fecha_hasta,NOW()) AS Days")])
    ->whereRaw('DATEDIFF(alq_fecha_hasta,NOW()) >= ?', [0])
    ->join('socio','alquiler.soc_id','=','socio.id')
    ->join('pelicula','alquiler.pel_id','=','pelicula.id')
    ->orderBy('Days','asc')->paginate(6);
    return $resting_time;
}

I hope it helps someone, thanks likewise to the people who responded cleared my mind a bit and gave me new things to try.

How can I filter a negative value from a query made with eloquent and then paginate it?

3 Answers3