Выбрать все, где последнее отношение верно?
У меня есть система, в которой многие пункты могут быть утверждены, и история утверждений можно увидеть на каждом элементе. Примером является этап пользователя, который может быть одобрен или отклонен. Таблицы в моей книге базы данных, как это:
+-----------------+------------------+
| Approvals |
+-----------------+------------------+
| Field | Type |
+-----------------+------------------+
| id | int(10) unsigned |
| approved | tinyint(1) |
| reason | text |
| approvable_id | varchar(191) |
| approvable_type | varchar(191) |
| created_at | timestamp |
| updated_at | timestamp |
+-----------------+------------------+
+----------------------+---------------------------------------+
| Milestones |
+----------------------+---------------------------------------+
| Field | Type |
+----------------------+---------------------------------------+
| id | int(10) unsigned |
| name | varchar(191) |
| created_at | timestamp |
| updated_at | timestamp |
+----------------------+---------------------------------------+
То, что я хочу сделать, это выбрать все этапы, на которых было принято последнее одобрение. Например, веха, возможно, была ранее принята, но позже отклонена, и в этом случае эта веха не должна появляться в принятом запросе, поскольку с тех пор она была отклонена.
Как я могу выбрать только те этапы, на которых принимается последнее одобрение? Подход, который я пробовал до сих пор, состоит в том, чтобы сделать exist подзапрос, который проверяет, есть ли одобрение вех, который принят. Однако, это также извлекает результаты, которые были приняты и позже отклонены:
SELECT * FROM `milestones`
WHERE EXISTS (
SELECT * FROM `approvals`
WHERE `milestones`.`id` = `approvals`.`approvable_id`
AND `approvals`.`approvable_type` = 'App\Models\Milestone'
AND `created_at` = (
SELECT MAX(created_at) FROM `approvals` AS `sub`
WHERE sub.approvable_id = approvals.approvable_id
AND sub.`approvable_type` = `approvals`.`approvable_type`
AND `sub`.`id` IN (
SELECT `id` FROM `approvals`
WHERE `approved` = 1
)
)
) AND `milestones`.`deleted_at` IS NULL
Можно ли захватить все вехи, где принимается последнее одобрение? Или это должно быть сделано на уровне приложения?
Решение
Это должно сделать работу
select m.*,a.*
from milestones m
join approvals a on m.id = a.approvable_id
join (
select approvable_id, max(created_at) created_at
from approvals
group by approvable_id
) aa on a.approvable_id = aa.approvable_id
and a.created_at = aa.created_at
where a.approved = 1 /* other filters in query */
Сначала он объединит каждый этап с последней записью из таблицы утверждений, а затем в разделе «Где» он отфильтровывает последние утверждения с утвержденными = 1
Другие решения
Поскольку вы пометили это с помощью Laravel и настроили изменяемые отношения, даже если вы пишете простой SQL, я собираюсь показать вам способ Laravel (Eloquent).
Если я вас правильно понимаю, используя Полиморфные отношения Ларавеля Я думаю, что это должно работать:
Approval.php
<?php
namespace App\Models;
use App\Models\Milestone;
use Illuminate\Database\Eloquent\Model;
class Approval extends Model
{
public function approvable()
{
return $this->morphTo();
}
}
Milestone.php
<?php
namespace App\Models;
use App\Models\Approval;
use Illuminate\Database\Eloquent\Model;
class Milestone extends Model
{
/**
* All approvals
*
* @return MorphMany
*/
public function approvals()
{
return $this->morphMany(Approval::class, 'approvable');
}
/**
* The most recent "approved" approval
*
* @return MorphMany
*/
public function lastestApproval()
{
return $this->morphMany(Approval::class, 'approvable')->where('approved', 1)->latest()->limit(1);
}
}
Тогда, чтобы получить все Основные этапы с их последнего одобрения:
$milestones = App\Models\Milestone::with('lastestApproval')->get();
Вот как выглядит структура БД (должна совпадать с вашей):
Как tptcat отметил, что этот вопрос помечен как вопрос Laravel. Итак, используя М Халид Джунаидс ответ я преобразовал его в синтаксис Eloquent (или столько, сколько мог) и получил следующее:
public function scopeApproved($query, $accepted=true)
{
return $query->select('milestones.*', 'approvals.*')
->join('approvals', 'milestones.id', '=', 'approvals.approvable_id')
->join(\DB::raw(
'(SELECT approvable_id, MAX(created_at) created_at
FROM approvals
GROUP BY approvable_id) aa' ), function($join)
{
$join->on('approvals.approvable_id', '=', 'aa.approvable_id')
->on('approvals.created_at', '=', 'aa.created_at');
})->where('approvals.approved', $accepted);
}