[FIXED] How to select every nth row in Laravel query builder

Issue

I have an API that fetches data using Laravel query builder. I make a call to this API from a react app. I have a new row every 5 minutes in my database and I need to be able to retrieve the data for a day or for a period (like a week or two). Making this call makes my react app very slow, even when I only fetch the data for a single day. I need to be able to select every nth row which would improve performance (as well as the readability of the chart I create based on the data...).

Instead of retrieving every record (every 5 minutes) I want to be able to retrieve 1 every 12 for example (every 60 minutes)

The time (log_time in my DB) is stored in a weird way: 103500 being 10:35 AM. In want to use a modulo in my query but I cannot make it work...

$data = DB::connection('mysql')->table('my_table')
    ->select('value','log_date','log_time')
    ->where('id_user',$userId)
    ->where('log_time', mod 500 = 0)    // This is the line I cannot get to work
    ->orderBy('log_date')
    ->orderBy('log_time')->get();

I tried different things for that line. Some were desperate attempts...

->where('log_time', mod 500 = 0)   // unexpected '500', expecting ')' 
->where('log_time mod 500 = 0')    // Unknown column 'log_time mod 500 = 0'
->where('log_time mod 500', 0)     // Unknown column 'log_time mod 500'
->where('log_time', % 500 = 0)     // unexpected '%', expecting ')' 
->where('log_time % 500 = 0')      // Unknown column 'log_time % 500 = 0'
->where('log_time % 500', 0)       // Unknown column 'log_time % 500'
->where('log_time', mod 500, 0)    // unexpected '500', expecting ')' 
->where('log_time', 'mod 500', 0)  // only return result for 'log_time' = 0 (midnight)
->where('log_time', % 500, 0)      // unexpected '500', expecting ')'
->where('log_time', '% 500', 0)    // only return result for 'log_time' = 0 (midnight)
->where('log_time', MOD(500,0))    // Call to undefined function MOD()

I also tried with ->whereRaw which didn't work either.

Solution

I cannot try it out at the moment but maybe a whereRaw could help?

->whereRaw('log_time MOD 500 = 0')

Answered By - Aless55

Answer Checked By - Marilyn (PHPFixing Volunteer)