Laravel – display users in foreach loop twice with ordered by date

I have a users table like this:

I would like to display a query, where every user is listed twice with different text (created_at / updated_at) and the date. The merged query should be ordered by date (created_at and updated_at).

Expected result

Zoey created at 2022-10-09 ...
Peter created at 2022-10-20 ...
Zoey updated at 2022-10-24 ...
Peter updated at 2022-10-31 ...

How to do that in Laravel 8? How should the foreach loop look like? Thanks!

>Solution :

You can use Laravel’s union for this:

$createdBy = User::select('name', 'created_at as display_date', DB::raw("'created' as action"));

$users = Users::select('name', 'updated_at as display_date', DB::raw("'updated' as action"))
->union($createdBy)
->orderBy('display_date')
->get();

This will return a Collection of User models like so:

array:6 [▼
  0 => array:3 [▼
    "name" => "Zoey"
    "display_date" => "2022-10-19 18:46:50"
    "action" => "created"
  ]
  1 => array:3 [▼
    "name" => "Peter"
    "display_date" => "2022-10-20 18:47:00"
    "action" => "created"
  ]
  2 => array:3 [▼
    "name" => "Zoey"
    "display_date" => "2022-10-24 18:46:55"
    "action" => "updated"
  ]
  3 => array:3 [▼
    "name" => "Peter"
    "display_date" => "2022-10-31 18:47:06"
    "action" => "updated"
  ]
]

Then you can loop this single Collection:

@foreach($users as $user)
  {{ $user->name . ' ' . $user->action . ' at ' . $user->display_date }}
@endforeach

Output would be:

Zoey created at 2022-10-19 18:46:50
Peter created at 2022-10-20 18:47:00
Zoey updated at 2022-10-24 18:46:55
Peter updated at 2022-10-31 18:47:06

Also for reference, this would be the SQL Query executed:

SELECT name, created_at as display_date, 'created' as `action` FROM users
UNION
SELECT name, updated_at as display_date, 'updated' as `action` FROM users
ORDER BY display_date;