Laravel 5 - Query with left join and subqueries

What I have

I have a working Query, which I can run successfully as plain SQL command on my SQL server.

SELECT
  i.hw_id
, i.hw_hostname
, i.hw_create_date created_at
, uc.username created_by
, he.hw_edited_date last_edit_date
, ue.username last_edit_by
FROM tbl_hw_inventar AS i
  INNER JOIN tbl_user AS uc ON i.hw_create_user_idfs = uc.id
  LEFT OUTER JOIN (
            SELECT
                  het.*
            FROM tbl_hw_edited AS het
                  INNER JOIN (
                        SELECT
                              hw_edited_client_idfs
                            , MAX(hw_edited_date) AS last_edit_dt
                        FROM tbl_hw_edited
                        GROUP BY hw_edited_client_idfs
                  ) AS mx ON het.hw_edited_client_idfs = mx.hw_edited_client_idfs
                              AND het.hw_edited_date = mx.last_edit_dt
      ) AS he ON i.hw_id = he.hw_edited_client_idfs
  LEFT OUTER JOIN tbl_user AS ue ON he.hw_edited_user_idfs = ue.id

Which gives me the following result:

| hw_id | hw_hostname | created_at          | created_by | last_edit_at        | last_edit_by |
| 1     | client-01   | 2015-09-28 08:15:36 | user1      | 2015-09-28 10:22:12 | user3        |
| etc. etc. etc.

What I need

Now I have to write this query as a laravel 5 command.

But there I don't know how.

The code

I tried with something like "where in" like this:

hardwareitems = DB::table('tbl_hw_inventar')
                        ->join('tbl_user', 'tbl_hw_inventar.hw_create_user_idfs', '=', 'tbl_user.id')
                        ->whereIn('tbl_hw_inventar.hw_id', function($query){
                            $query->select(DB::raw('MAX(hw_edited_id)'))
                                    ->from('tbl_hw_edited')
                                    ->whereRaw('tbl_hw_edited.hw_edited_client_idfs = tbl_hw_inventar.hw_id');
                        })
                        ->select('tbl_hw_inventar.*', 'tbl_hw_typ.hw_typ_title', 'tbl_user.username', 'tbl_hw_edited.*')
                        ->orderBy('tbl_hw_inventar.hw_id', 'asc')
                        ->get();

This code snippet is from a try with a smaller query, as I just wrote down the right query.

How can I convert my SQL query into the laravel query?

Thanks for any help in advance

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/1VlhPmU
via IFTTT