Current Revision Query

Article.php

<?php

/**
 * The problem : i want to retrieve articles with their current revision of the status of Pending.
 *
 * Go to the controller, see that im using the scope of `hasType`, follow through into the article model
 * you will see that we are using a `whereHas` . Whats funny is its working in the fact if `NONE` of the article revisions 
 * have a status of pending it works! But if `1` has a status of `pending` it will return the latest revision even if its not 
 * pending
 */

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Article extends Model
{

    protected $guarded = ['id'];

    const STATUSES = [
        'Draft' => 'draft',
        'Pending' => 'pending',
        'Declined' => 'declined',
        'Approved' => 'approved'
    ];

    /*
    |--------------------------------------------------------------------------
    | Relations
    |--------------------------------------------------------------------------
    */

    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function revisions()
    {
        return $this->hasMany(ArticleRevision::class);
    }

    public function currentRevision()
    {
        return $this->hasOne(ArticleRevision::class)->latest('id');
    }

    /*
    |--------------------------------------------------------------------------
    | Scopes
    |--------------------------------------------------------------------------
    */

    public function scopeOfType($query, $type)
    {
        return $query->whereHas('currentRevision', function($query) use($type) {
            $query->where('status', $type);
        });
    }
}

ArticleRevision.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class ArticleRevision extends Model
{
    protected $guarded = ['id'];
  
    /* 
    |--------------------------------------------------------------------------
    | Relations
    |--------------------------------------------------------------------------
    */

    public function article()
    {
        return $this->belongsTo(Article::class);
    }
  
}

SomeController.php

<?php

namespace App\Http\Controllers;

use App\Models\Article;
use Illuminate\Http\Request;

class AdminArticlesController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @param Request $request
     * @return \Illuminate\Http\Response
     */
    public function index(Request $request)
    {

        dd($articles = Article::with('currentRevision')
            ->ofType(Article::STATUSES['Pending'])
            ->where('id', '3')
            ->first()->currentRevision->status
        );
      
       // still gives article revision status of draft!
    }
  
}

Here is my database

Solution

 public function scopeOfType($query, $type)
    {
       return $query->wherehas('currentRevision', function($query) use($type) {
           $query->whereRaw('id = (select id from article_revisions where article_revisions.article_id = articles.id order by id desc limit 1)')
           ->where('status', $type);
       });
    }