Get Laravel Eloquent Collection from raw query

Question

I'm using Laravel 5 and I have a table of users, and two tables 'clients' and 'employes' which contain relations between users.

I would like to get all the clients and employees of the logged in user.

I have a raw query which works fine for that:

select users.* from clients, users
where clients.id_marchand = 8 and users.id = clients.id_client 
union 
select users.* from employes, users 
where employes.id_marchand = 8 and users.id = employes.id_employe 
order by `seen` asc, `created_at` desc limit 25 offset 0

Raw queries are returning an array, but I need to get an Eloquent Collection like :

return $this->model
->where(...)
->oldest('seen')
->latest()
->paginate($n);

I've tried a lot of different possibilities, but none of them is working...

Isn't there a way to do that with subqueries, or something else ?

Answer 1

You can run the query and then populate it into a model with hydrate: eg:

$userData = DB::select('SELECT * FROM users ...');
$userModels = User::hydrate($userData);

Answer 2

You can just convert the query results to a collection using collect()

$users = \DB::select( "SELECT users.*
FROM clients,
       users
  WHERE clients.id_marchand = 8
    AND users.id = clients.id_client
  UNION
  SELECT users.*
  FROM employes,
       users
  WHERE employes.id_marchand = 8
    AND users.id = employes.id_employe
  ORDER BY `seen` ASC,
           `created_at` DESC LIMIT 25
  OFFSET 0" );

return collect( $users );

If more than the results being a collection of models you should use hydrate() (https://laravel.com/api/5.3/Illuminate/Database/Eloquent/Model.html#method_hydrate) For the example you provided, the code should be the following:

$users = \DB::select( "SELECT users.*
  FROM clients,
       users
  WHERE clients.id_marchand = 8
    AND users.id = clients.id_client
  UNION
  SELECT users.*
  FROM employes,
       users
  WHERE employes.id_marchand = 8
    AND users.id = employes.id_employe
  ORDER BY `seen` ASC,
           `created_at` DESC LIMIT 25
  OFFSET 0" );

return User::hydrate($users);

Please just note that this method is slower and for a big set of data, this approach could crash if the result is too big to get allocated in ram