Выберите все, где последнее отношение верно?
У меня есть система, в которой можно утверждать многие элементы, и историю утверждений можно увидеть по каждому элементу. Примером может служить контрольная точка пользователя, которая может быть одобрена или отклонена. Таблицы в моей книге базы данных выглядят следующим образом:
+-----------------+------------------+
| 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, который проверяет, есть ли у milestones одобрение, которое принято. Однако это также позволяет получить результаты, которые были приняты, а затем отклонены:
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
Можно ли охватить все этапы, на которых принято последнее утверждение? Или это должно быть сделано на уровне приложения вместо этого?
3 answers
Это должно выполнить свою работу
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 */
Сначала он соединит каждую веху с последней записью из таблицы утверждений, а затем в предложении where отфильтрует последние утверждения с утвержденным = 1
Поскольку вы пометили это с помощью Laravel и настроили изменяемые отношения, даже если вы пишете простой SQL, я собираюсь показать вам способ Laravel (Красноречивый).
Если я правильно вас понимаю, используя Полиморфные отношения Ларавеля, я думаю, что это должно сработать:
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. Поэтому, используя Ответ М Халида Джунаида , я преобразовал его в Красноречивый синтаксис (или, насколько мог) и получил следующее:
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);
}