不使用DB::raw()的Laravel 5.8雄辩查询--根据多对多关系的最新值获取行

Question

我的目标是选择所有标记为假阳性的问题。这些问题通过与透视表IssuesStatus的ManyToMany关系连接到Status。问题的当前状态由Status表上status列的值确定。

我想出了一个有效的解决方案，但似乎有些可疑。我正在寻找一种使用Eloquent query builder重写查询的方法，而不依赖于DB::raw()方法。

public function getFalsePositivesAttribute() {
    return Issue::where(DB::raw(
        '( select `status` '.
        'from `issues-status` '.
        'left join `status` on `status`.id = `issues-status`.status_id '.
        'where `issues-status`.issue_id = `issues`.id '.
        'order by `issues-status`.id desc limit 1 )'
    ), '=', 'false-positive')->get();
}

所需SQL查询的示例：

SELECT
    `Issues`.id
FROM
    `issues` AS `Issues`
LEFT JOIN
    `issues-status` `IssueStatus` on `Issues`.id = `IssueStatus`.issue_id
LEFT JOIN
    `status` as `StatusTable` on `IssueStatus`.status_id = `StatusTable`.id
WHERE
    `Issues`.report_id = 2
AND
    ( 
        SELECT
            `status`
        FROM
            `issues-status` `IssueStatus`
        LEFT JOIN
            `status` `StatusTable` on `StatusTable`.id = `IssueStatus`.status_id
        WHERE
            `IssueStatus`.issue_id = `Issues`.id
        ORDER BY
            `IssueStatus`.id desc
        LIMIT 1
    ) = 'false-positive'
GROUP BY
    `Issues`.id

型号：

class Issue extends Model {
    ...

    public function status() {
        return $this->belongsToMany( Status::class, 'issues-status')
            ->withTimestamps()
            ->withPivot('note');
    }

    ...
} 

class Status extends Model {
    ...

    public function issues() {
        return $this->hasMany(Issue::class);
    }

    ...
}

表：

Issues:
  id - identity

Status
  id - identity
  status - string

IssueStatus
  id - identity
  issue_id - relation to Issues
  status_id - relation to Status
  created_at - timestamp
  note - text

Answer 1

如果我理解正确的话，您希望获取最新状态等于某项的问题。

向您的Issues模型添加一个latestStatus函数：

public function latestStatus()
{
    return $this->hasMany(IssueStatus::class)->latest();
}

现在，将您的status函数替换为以下函数：

public function status() {
    return $this->belongsToMany( Status::class, 'issues-status')
       ->withTimestamps()
       ->withPivot('note');
}

然后：

//Get all issues which has the desired status
Issue::whereHas('status', function($query){
    $query->where('id', $desiredStatusId);
})
->get()
->reject(function($issue){ 
    //filter the collection by the latest issue status,
    // and reject those who does not have the desired latest status;
    return $issue->latestStatus()->first()->status_id != $desiredStatusId;
});

希望能有所帮助。