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

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.

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

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.