title: subquery with join in laravel
body: <p>I'm about to go crazy about a query, can someone help me to translate a query to work in laravel, or to write in "laravel way".</p>
  
  <p>The query code is below, and it's using MySQL.</p>
  
  <p>The part that I'm stuck is in the first JOIN with the subquery to the other JOIN it.</p>
  
  <pre><code>SELECT 
      usr.token,
      usr.id,
      usr.username,
      usr.sn,
      usr.givenname,
      usr.telefone_numero,
      usr.active,
      usr.unidade_id,
      usr.divisao_id,
      usr.secao_id,
      clients.nick,
      posto_grad.sigla AS quadro_sigla,
      log_logradouro_complemento,
      log_logr.nome AS log_logradouro_nome,
      log_logr.cep AS log_logradouro_cep,
      log_bairro.nome AS log_bairro_nome,
      log_localidade.nome AS log_localidade_nome,
      log_uf.sigla AS log_uf_sigla
  FROM
      users usr
          LEFT JOIN
      (SELECT 
          usr_log1.*
      FROM
          users_log_logradouro AS usr_log1
      LEFT JOIN users_log_logradouro AS usr_log2 ON usr_log1.user_id = usr_log2.user_id
          AND usr_log1.created_at &lt; usr_log2.created_at
      WHERE
          usr_log2.user_id IS NULL) AS temp ON (usr.id = temp.user_id)
          LEFT JOIN
      log_logradouro log_logr ON temp.log_logradouro_id = log_logr.id
          INNER JOIN
      clients ON clients.matricula = usr.givenname
          INNER JOIN
      quadro ON quadro.id = clients.quadro_id
          INNER JOIN
      posto_graduacao posto_grad ON posto_grad.id = clients.posto_graduacao_id
          LEFT JOIN
      log_bairro ON log_logr.log_bairro_id = log_bairro.id
          LEFT JOIN
      log_localidade ON log_bairro.log_localidade_id = log_localidade.id
          LEFT JOIN
      log_uf ON log_localidade.log_uf_sigla = log_uf.sigla
  WHERE
      usr.departmentnumber IN (1,2,3,4)
  </code></pre>
  
  <hr>