Question 220587 in Laravel Question posted in
The official Laravel Documentation can be found here.

Max of date from three tables in Laravel

I have three tables in database

tableA
tableB
tableC

All these three table have document_date.
I need eloquent with model for

max(
 ( max of document_date in table A) Union
 ( max of document_date in table B) Union
 ( max of document_date in table C)
)

The result should be one date.

Tags:

2

Answers


  1. 0

    you should join those tables then use mysql greatest function:

    $maxDate = TableA::join('tableB', 'tableA.user_id', '=', 'tableB.user_id')
    ->join('tableC', 'tableB..user_id', '=', 'tableC.user_id')
    ->selectRaw('tableA.user_id,GREATEST(MAX(tableA.document_date), MAX(tableB.document_date), MAX(tableC.document_date)) as final_max_date')
    ->groupBy('tableA.user_id')
->get();

    I think it need some tweaks to match your DB, but there is the main idea

    Login or Signup to reply.
  2. 0

    I’d execute three separate queries selecting the largest date per table and then checking which one is the highest using plain PHP. Otherwise your queries will quickly become a tangled mess. Unless this is for some highly optimized production application, the premature optimization will definitely not outweigh the speed gained by combining it into a singular query.

    Example

    function maxFromTable(array $models) {
    $numbers = [];

    foreach($models as $model => $field) {
        $numbers[] = $model::latest($field)->field;
    }
    
    return max($numbers);
}

$models = [
    tableA::class => 'document_date',
    .....
];

maxFromTable($models);

    Psuedo-code

    Pro’s

    1. Easier to extent
    2. More readable

    Cons

    1. Slower
    2. More database overhead
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search