Laravel eloquent query based on a given time-CodePudding

I have a simple list of tasks that need to be completed today, at various times. Each task has a reminder/notification that needs to be sent x amount of minutes before it is planned.

Simplified migration is as follows:

Schema::create('user_tasks', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->time('time'); // The time for when the task has been planned, i.e. 10:00
    $table->string('minutes'); // The minutes before a reminder needs to be send, i.e. 30
    $table->timestamps();
});

I want to query only the tasks that are due for a reminder, based on the current time and the minutes for the given task. So, for example, I have a task called sweep the floors with a value of 30 for the minutes. Which means, 30 minutes before the task is due, I want to send a reminder.

I could query all the tasks and then check for each task if it is due, but I want to query only the tasks that are due for a reminder, obviously.

This is my query so far:

$tasks = Task::whereTime('time', '<', Carbon::now()->addMinutes($task->minutes)->format('H:i'))->get();

Obviously $task->minutes doesn't exist, the value is in the database but I am unsure how to make my query. Any pointers?

CodePudding user response：

You would need to compare column 'time' and current time column 'minutes'. So basically you need to compare 2 columns. For this instead if whereTime you can use whereRaw(), for example something like this :

$tasks = Task::whereRaw('time < DATE_ADD("' . now() . '", interval minutes minute)')->get();

DATE_ADD is a MySQL function which adds time to it's first parameter.

CodePudding user response：

For me, the best way to do this would be by refactoring your table schema to this:

$table->id();
$table->string('name');
$table->time('time'); // The time for when the task has been planned
$table->integer('minutes'); // The minutes before a reminder
$table->time('time_notification');//you can do the query only of this column
$table->timestamps();

So you would be able to get them by this query:

$tasks = Task::whereTime('time_notification', '<=', Carbon::now()->format('H:i'))->get();