mysql - Create Eloquent model from complex raw SQL-query -

i thinking right design of method gives user list of clients who's last appointment long time ago.

so have 2 table (simplified):

• clients (id, first_name)
• appointments (id, client_id, datetime)

what trying do: list of 3 clients who's last appointment long time ago.

so do: select users oldest appointments , return them (with complex sql query). create models them.

is design case?

use illuminate\database\eloquent\collection;  class clientrepository {      /**      * clients no appointments in near history (sort date of last appointment asc)      *      * @todo make better way find falling clients (more laravelish maybe?)      * @param $count how many clients should method return      * @return illuminate\database\eloquent\collection       */     static public function getlost($count=3) {          //this sql looks long works charm         //solution based on http://stackoverflow.com/questions/1066453/mysql-group-by-and-order-by         $sql = "             select * (                 select clients.*, clients.id cli_id , appointments.datetime last_appointment_datetime                 clients                 inner join appointments on clients.id=appointments.client_id                 order appointments.datetime asc             ) tmp_table             group cli_id             order last_appointment_datetime asc             limit ?         ";          $users = \db::select($sql,[$count]);         foreach($users $key=>$user) {             $user_array = (array)$user;             $users[$key] = new client();             $users[$key]->forcefill($user_array);         }         $collection = new collection($users);          return $collection;     }  } 

you can without subselect , using eloquent's query builder:

//select clients joined appointments $users = client::join('appointments', 'appointments.client_id', '=', 'clients.id')    //group rows client id (if client has multiple appointments client appear multiple times in result set   ->groupby('clients.id')    //sort them date of last appointment in descending order   ->orderbyraw('max(appointments.datetime)')    ->get();