基于PostgreSQL与Laravel，求最新状态为pending的文件SQL查询方案

Hey there! Let's work through this problem together. You're trying to pull all media_files where their most recent status is 'pending', ignoring any files that never had a pending status or whose latest status is something else. Let's first break down what was off with your initial query, then build the correct solution.

What's Wrong with the Initial Query? #

Your original SQL has a few key issues that are causing it to run incorrectly:

• Duplicate Records: The INNER JOIN with statuses will return multiple rows for a single file if it has multiple status entries (since each status gets joined to the file).
• Model Type Mismatch: Your subquery uses model_type = 'App\\File' but the join uses model_type = 'App\\Entities\\Media\\File'—these need to match, otherwise the subquery might not find the right status for your files.
• Missing Filter: You're not filtering to only keep files where the latest status is actually 'pending'—right now it's returning all files with any status, just showing their latest status.

Solution 1: Use Window Functions (PostgreSQL-Friendly, Efficient) #

PostgreSQL supports window functions which are perfect for picking the latest status per file. Here's the corrected query:

SELECT mf.*, s.name AS latest_status
FROM media_files mf
INNER JOIN (
    SELECT 
        model_id, 
        name,
        -- Assign a row number to each status entry, ordered by newest first per file
        ROW_NUMBER() OVER (PARTITION BY model_id ORDER BY created_at DESC) AS rn
    FROM statuses
    WHERE model_type = 'App\\Entities\\Media\\File' -- Ensure this matches your actual model type
) s ON mf.id = s.model_id AND s.rn = 1
WHERE s.name = 'pending' -- Only keep files where latest status is pending
ORDER BY mf.id DESC;

How This Works:

• The subquery uses ROW_NUMBER() to assign a number to each status entry grouped by model_id (your file ID). The newest status gets rn = 1.
• We join this subquery back to media_files only where rn = 1 (so we get just the latest status per file).
• Finally, we filter to only keep rows where the latest status is 'pending'.

Solution 2: Using a Subquery for Latest Timestamp #

If you prefer a more traditional subquery approach, this also works (though window functions are usually more efficient for large datasets):

SELECT mf.*, s.name AS latest_status
FROM media_files mf
INNER JOIN statuses s ON mf.id = s.model_id
WHERE s.model_type = 'App\\Entities\\Media\\File'
  -- Match the latest status timestamp for the file
  AND s.created_at = (
      SELECT MAX(created_at) 
      FROM statuses 
      WHERE model_id = mf.id AND model_type = 'App\\Entities\\Media\\File'
  )
  AND s.name = 'pending' -- Filter for pending status
ORDER BY mf.id DESC;

Bonus: Laravel Eloquent Version #

Since your app is built on Laravel, here's how you can implement this using Eloquent (matches the window function approach):

use App\Models\MediaFile;
use Illuminate\Database\Query\Builder;

$pendingFiles = MediaFile::query()
    ->select('media_files.*', 'latest_status.name as latest_status')
    ->joinSub(
        function (Builder $query) {
            $query->select('model_id', 'name')
                ->selectRaw('ROW_NUMBER() OVER (PARTITION BY model_id ORDER BY created_at DESC) AS rn')
                ->from('statuses')
                ->where('model_type', 'App\\Entities\\Media\\File');
        },
        'latest_status',
        function ($join) {
            $join->on('media_files.id', '=', 'latest_status.model_id')
                 ->where('latest_status.rn', 1);
        }
    )
    ->where('latest_status.name', 'pending')
    ->orderBy('media_files.id', 'desc')
    ->get();

Key Reminder #

Double-check that the model_type value in your query exactly matches what's stored in the statuses table. The mismatch in your initial query was likely a big part of why it wasn't working as expected!

内容的提问来源于stack exchange，提问作者Riz