title: laravel subquery select, tables with composite primary key
body: <p>I am trying to pass an sql query to laravel but in this query the tables are joined using composite primary keys and additionally it has subqueries to these tables so I don't know how to do it</p>
  
  <p>The sql code is as shown below</p>
  
  <pre><code>            select distinct g.id,
              (   
                  select count(*) from xxx s(nolock) inner join yyy t(nolock) 
                  on t.id=s.id 
                  and t.year=s.year 
                  and t.code=s.code 
                  where s.year = 2017 
                  and t.status&lt;&gt;'C'
                  and s.created_at&gt;=convert(datetime,'16/08/2017 00:00:00',103) 
                  and s.created_at&lt;=convert(datetime,'22/08/2017 23:59:59',103) 
                  and s.id=g.id
              ) as xxx,
              (
                  select count(*) from zzz s(nolock) inner join yyy t(nolock) 
                  on t.id=s.id 
                  and t.year=s.year 
                  and t.code=s.code 
                  where s.year = 2017 
                  and t.status&lt;&gt;'C'
                  and s.created_at&gt;=convert(datetime,'16/08/2017 00:00:00',103) 
                  and s.created_at&lt;=convert(datetime,'22/08/2017 23:59:59',103) 
                  and s.id=g.id
              ) as zzz
              from globals g(nolock) order by g.id
  </code></pre>
  
  <p>It has many subqueries like these two that I put here, same conditions in the joins and same where clause but different tables, any idea how to do this in laravel?</p>