如何通过Laravel Query Builder从Union结果中获取最小/最大值？

Here's how you can translate your raw MySQL query into clean, maintainable Laravel Query Builder syntax, with explanations along the way:

Step 1: Build the Two Subqueries #

First, create separate queries for each table, filtering out null values and aliasing columns where needed:

// Subquery 1: Fetch non-null PromisedDate values from JobWorkOrder
$workOrderDates = JobWorkOrder::whereNotNull('PromisedDate')
    ->select('PromisedDate');

// Subquery 2: Fetch non-null ScheduledDate (aliased as PromisedDate) from JobPhase
$phaseDates = JobPhase::whereNotNull('ScheduledDate')
    ->selectRaw('ScheduledDate as PromisedDate');

Quick note: I used whereNotNull instead of your original where('ScheduledDate', '<>', null) because comparing values to null with <> doesn’t work as expected in SQL (since null <> null evaluates to unknown). whereNotNull is the correct, readable way to filter out nulls in Laravel.

Step 2: Combine Queries with Union #

Merge the two subqueries using Laravel’s union method—this replicates the UNION logic from your raw SQL, removing duplicate values by default (use unionAll if you want to keep duplicates):

$combinedDates = $phaseDates->union($workOrderDates);

Step 3: Calculate the Minimum Date #

Wrap the combined union query as a subquery, then use the MIN() aggregate function to get the smallest date:

// Get the minimum date as a scalar value (e.g., a Carbon instance or string)
$minimumDate = DB::table($combinedDates, 'ScheduledTable')
    ->selectRaw('MIN(PromisedDate) as min_date')
    ->value('min_date');

Concise Chained Version #

If you prefer a more compact format, you can chain all steps together:

$minimumDate = DB::query()
    ->fromSub(
        JobPhase::whereNotNull('ScheduledDate')
            ->selectRaw('ScheduledDate as PromisedDate')
            ->union(
                JobWorkOrder::whereNotNull('PromisedDate')->select('PromisedDate')
            ),
        'ScheduledTable'
    )
    ->selectRaw('MIN(PromisedDate)')
    ->value('MIN(PromisedDate)');

How This Matches Your Raw SQL #

• The fromSub method creates the ScheduledTable alias exactly like your original subquery.
• union combines the two result sets just like UNION in MySQL.
• selectRaw lets you use the MIN() function directly, and value() retrieves the single result value instead of a full row object.

This approach stays true to your original logic while using Laravel’s query builder features for readability and maintainability.

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