1

I want to get a min value from union table.

As a MySQL Query like below.

SELECT MIN(PromisedDate) 
FROM (SELECT PromisedDate FROM JobWorkOrder WHERE `PromisedDate` IS NOT NULL
    UNION
    SELECT ScheduledDate AS PromisedDate FROM JobPhase WHERE `ScheduledDate` IS NOT NULL) ScheduledTable;

This Query's result is min value of PromisedDate and ScheduledDate in JobWorkOrder and JobPhase Table.

So I built a Laravel query like below.

$JobPhase = JobPhase::where('ScheduledDate', '<>', null)
    ->select('ScheduledDate AS PromisedDate');

$minDate = JobWorkOrder::where('PromisedDate', '<>', null)
    ->select('PromisedDate')
    ->union($JobPhase)
    ->min('PromisedDate');

But I got a wrong result. The result is 

(SELECT MIN(`PromisedDate`) FROM `JobWorkOrder` WHERE `PromisedDate` IS NOT NULL) 
union 
(SELECT `ScheduledDate` AS `PromisedDate` FROM `JobPhase` WHERE `ScheduledDate` IS NOT NULL)

How can I build a Laravel query?

HiKangg
  • 251
  • 1
  • 4
  • 19

2 Answers2

2

The min field is selected from subquery:

$JobPhase = JobPhase::where('ScheduledDate', '<>', null)
    ->select('ScheduledDate AS PromisedDate');

$sub = $JobWorkOrder = JobWorkOrder::where('PromisedDate', '<>', null)
    ->select('PromisedDate')
    ->union($JobPhase);

$minDate = DB::table( DB::raw("({$sub->toSql()}) as sub") )
    ->mergeBindings($sub->getQuery())
    ->min('PromisedDate');

See also: How to select from subquery using Laravel Query Builder?

Ben
  • 5,069
  • 4
  • 18
  • 26
1

You can get min from the get() function result. Simply added a get() function to your query.

$JobPhase = JobPhase::where('ScheduledDate', '<>', null)
    ->select('ScheduledDate AS PromisedDate');

$minDate = JobWorkOrder::where('PromisedDate', '<>', null)
    ->select('PromisedDate')
    ->union($JobPhase)
    ->get()
    ->min('PromisedDate');

Then you will get a MIN value.

motherland
  • 28
  • 4