'Laravel query builder inject column value into Carbon query

I've got a query that I'm running in my project on a model called Domain, it returns all domains where the status column of a domain matches complete and a date column is before or equal to a specific set of days.

I've recently refactored the model columns and have included a new column called domain_alert_period which is an integer column that holds a value in days, I need to somehow target this column and put it in replace of my existing 30 so that the user is able to control the number of days prior to getting an alert.

What am I missing from this to achieve this?

return Domain::where('status', 'complete')
            ->where(function ($sql) {
              $sql->where('domain_expires_on', '>', Carbon::now()->subDays(2)) // 2 days after expiry
                  ->where('domain_expires_on', '<', Carbon::now()->addDays(30)); // 30 days before expiry
            })
            ->get();
phplaravellaravel-query-builderphp-carbon


Solution 1:[1]

I believe you can use whereRaw() in your clause to use raw DB expressions, If its MySQL you can use DATE_ADD method with your column value for comparision

$sql->where('domain_expires_on', '>', Carbon::now()->subDays(2)) // 2 days after expiry
    ->whereRaw('domain_expires_on < DATE_ADD(NOW(), INTERVAL domain_alert_period DAY)');

Solution 2:[2]

  • A mysql DATE_SUB function should be used to subtract days from a domain_expires_on date. so that will be a date you should start an alert from

  • And then match a current date is greater or equal to that alert date

    $query = $this->model
              ->whereRaw('CURDATE() <= DATE_ADD(`domain_expires_on` , INTERVAL 2 DAY)') // till 2 days after expiry
              ->whereRaw('CURDATE() >= DATE_SUB(`domain_expires_on` , INTERVAL domain_alert_period DAY)')
              ->get();

Solution 3:[3]

If i understand your question correctly what you can do is use use ($var) in your query to replace 30

$start=1; //you can also take date from user for between like `date_from` and `date_to` and parse them with Carbon insted of adding or substracting days from now()
$end=30;
return Domain::where('status', 'complete')
->where(function ($sql) use ((int) $start,(int) $end) {
     $sql->whereDate('domain_expires_on', '>', Carbon::now()->subDays($start)->toDateString())
     ->whereDate('domain_expires_on', '<', Carbon::now()->addDays($end)->toDateString());
})
->get();

Also if you are storing domain_expires_on only as the date you should format the carbon date to match your database format.

above method will work for $table->date('domain_expires_on'); and $table->dateTime('domain_expires_on');

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3 Pushkraj Jori

Related Questions