php - 从原始查询中获取 Laravel Eloquent Collection

Question

我正在使用 Laravel 5，我有一个用户表，以及两个包含用户之间关系的表“clients”和“employes”。

我想获取登录用户的所有客户和员工。

我有一个原始查询，它可以正常工作:

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

原始查询返回一个数组，但我需要得到一个 Eloquent 集合，例如:

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

我尝试了很多不同的可能性，但都没有用...

有没有办法通过子查询或其他方法做到这一点？

Answer 1

您可以使用 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 );

如果结果不仅仅是模型的集合，您应该使用 hydrate() ( https://laravel.com/api/5.3/Illuminate/Database/Eloquent/Model.html#method_hydrate )对于您提供的示例，代码应如下所示:

$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