Select all where latest relation is true?

user_1317771 0
Viewed 255

I have a system in which many items can be approved and the history of approvals can be seen on each item. An example is a user's milestone, which can be approved or rejected. The tables in my database book like this:

+-----------------+------------------+
| 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                             |
+----------------------+---------------------------------------+

What I want to be able to do is fetch all milestones where the last approval is accepted. For example a milestone may have been previously accepted, but later reject, in which case this milestone should not appear in the accepted query, as it has since been rejected.

How can I only fetch milestones in which the latest approval is accepted? The approach I have tried so far is to make an exist sub query which checks if milestones has an approval, that is accepted. However, this also fetches results which have been accepted and later rejected:

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

Is it possible to grab all milestones where the latest approval is accepted? Or would this have to be done on the application level instead?

laravel greatest-n-per-group mysql sql php
3 Answers

Since you tagged this with Laravel and you've set up the morphable relationships, even though you're writing plain SQL, I'm going to show you the Laravel (Eloquent) way.

If I'm understanding you correctly, using Laravel's Polymorphic Relationships I think this should work:

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

Then to get all Milestones with their most recent approval:

$milestones = App\Models\Milestone::with('lastestApproval')->get();

Here's what the DB structure looks like (should be the same as yours):

milestones enter image description here

user_11771910

This should do the job

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 */

First it will join each milestone with the latest record from approvals table and then in where clause it filter out the latest approvals with approved = 1

user_8533600

As tptcat pointed out, this question is tagged as a Laravel question. So using M Khalid Junaid's answer I converted it into Eloquent syntax (Or as much as I could) and got the following:

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