How to create a complexe virtual column in an Eloq

Question

This question is related to this one, but I simplified too much my real problem. So I have created a separated question:

I have two tables. One holding intervals in days associated to a name such as bimonthly = 15 and one other listing periodic actions and associated to an interval.

I would like to list the next periodic actions which is easy in pure SQL:

SELECT *, DATE_ADD(updated_at, INTERVAL days DAY) AS next_action 
FROM periodic_actions 
INNER JOIN intervals ON interval_id = intervals.id
HAVING next_action > NOW()

Here my tables:

CREATE TABLE `intervals` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `days` INT NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `periodic_actions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `interval_id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `updated_at` TIMESTAMP NOT NULL,
  PRIMARY KEY (`id`));

And the content of intervals:

+----+----------+------+
| id | name     | days |
+----+----------+------+
|  1 | daily    |    1 |
|  2 | monthly  |   30 |
|  3 | annually |  365 |
+----+----------+------+

I would like to build the same query with Eloquent where I can use where on my virtual column next_action:

Action::orderBy('next_action', 'asc')
    ->where('next_action', '>', Carbon::now())
    ->get()

The closest I have, which I cannot hide into my Model is:

Action::join('intervals', 'interval_id', '=', 'intervals.id')
    ->select(['*', DB::RAW('DATE_ADD(updated_at, INTERVAL days DAY) AS next_action')])
    ->havingRaw(DB::RAW('next_action > NOW()'))->get();

Of course I have this base Model:

class Action extends Model
{
    protected $table = 'periodic_actions';

    public function interval() 
    {
        return $this->belongsTo(Interval::class);
    }
}

Answer 1

Solution 1: Using a global scope to add the field to your select statements

The one issue with virtual columns is Eloquent will try to update them if you don't prevent it. You can exclude it from the $fillable array and use a global scope in your model to add the virtual column, something like:

protected static function boot()
{
    parent::boot();

    static::addGlobalScope('next_action', function (Builder $builder) {
        $builder->addSelect(DB::raw('DATE_ADD(updated_at, INTERVAL days DAY) AS next_action'));
    });
}

You'll have to keep in mind that this will couple your Model to MySQL since the date functions you're using won't work in databases like sqlite.

Solution 2: Using a MySQL view

Normally what I do when I have multiple calculated fields is create a MySQL view. Create the view with all the calculated fields you want, then you can create a new Eloquent model for that view without having to worry about query scopes.

The one caveat is you of course can't use create/update/delete on this model, but if you're only using the model for display purposes, that shouldn't be a problem.