如何在雄辩模型中创建复杂虚拟列? [英] How to create a complexe virtual column in an Eloquent Model?

问题描述

这个问题与这个问题 a>，但是我简化了我的真正问题.因此，我创建了一个单独的问题:

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

我有两个桌子.一个保持间隔(以天为单位)与一个名称(例如bimonthly = 15)相关，另一个保持间隔列出了周期性操作并与一个间隔相关联.

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.

我想列出在纯SQL中容易实现的下一个周期性操作:

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()

这是我的桌子:

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

间隔的内容:

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

我想用Eloquent构建相同的查询，在这里我可以在虚拟列next_action上使用where:

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

推荐答案

解决方案1:使用全局范围将该字段添加到您的选择语句中

关于虚拟列的一个问题是Eloquent，如果您不阻止，则会尝试更新它们.您可以将其从$ fillable数组中排除，并在您的全局范围中使用模型以添加虚拟列，例如:

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

您必须记住，这会将您的Model耦合到MySQL，因为您使用的日期函数在sqlite之类的数据库中不起作用.

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.

解决方案2:使用MySQL视图

通常，当我有多个计算字段时，我要做的是创建一个MySQL视图.使用所需的所有计算字段创建视图，然后可以为该视图创建一个新的Eloquent模型，而不必担心查询范围.

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.

一个警告是，您当然不能在此模型上使用create/update/delete，但是，如果仅将模型用于显示目的，那应该不是问题.

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.

这篇关于如何在雄辩模型中创建复杂虚拟列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！