Sort a mix of created_at and updated_at columns in the same query in Laravel-CodePudding

I'm using Laravel 8. I would like to have a query, where all items from the users table are listed twice, one with created_at column, and second with updated_at column. The result of this array should be in an ascending date order (mix of created_at and updated_at dates).

My table:

Expected result:

Zoey  2022-10-19 ...
Peter 2022-10-20 ...
Zoey  2022-10-24 ...
Peter 2022-10-31 ...

How to do that in Laravel 8?

CodePudding user response：

First, create two queries:

$result_one = DB::table('table')->select(['name','created_at AS field_date'])->get();
$result_two = DB::table('table')->select(['name','updated_at AS field_date'])->get();

Then merge the collections

$results = $result_one->merge($result_two);

And then you can sort them.

$sorted_result = $results->sortBy('field_date');

CodePudding user response：

In order to accomplish this, you can use the union() method in Eloquent. Also, be sure to use selectRaw() so you can alias the date columns as date. The union will need the columns to have the same name:

$usersCreatedAt = User::selectRaw('name, created_at AS date');
$usersUpdatedAt = User::selectRaw('name, updated_at AS date');

$users = $usersCreatedAt->union($usersUpdatedAt)->orderBy('date')->get();

The result would be a collection like this:

Illuminate\Database\Eloquent\Collection {#5108
     all: [
       App\Models\User {#5451
         name: "Zoey",
         date: "2022-10-19 18:46:50",
       },
       App\Models\User {#5467
         name: "Peter",
         date: "2022-10-20 18:47:00",
       },
       App\Models\User {#5443
         name: "Zoey",
         date: "2022-10-24 18:46:55",
       },
       App\Models\User {#5459
         name: "Peter",
         date: "2022-10-31 18:47:06",
       },
     ],
   }

Then you can get the array from it using ->toArray():

return $users->toArray();

Edit: Another good solution by @aynber. That solution uses Query Builder. So it depends on your code style on which you use :)