title: Laravel Query Builder MySQL - Count /w Group By
body: <p>Really struggling with the query and look around I can't find the answer that I require!</p>
  
  <p>I have 4 tables, offers, users, savedoffers and usedoffers.
  savedoffers/usedoffers tables are relational tables between users &amp; offers.</p>
  
  <p>I want to return a list of offer then left join usedoffers and savedoffers. Then group them by the offer (say offer id) and then count how many users have saved the offers and how many have used them.</p>
  
  <p>After all my efforts I am here...</p>
  
  <pre><code>$offers = DB::table('offers')
              -&gt;select('offers.id as id', 
                       'offers.code', 
                       'offers.title', 
                       'offers.date', 
                        DB::raw('count(usedoffers.id) as used_count'),
                        DB::raw('count(savedoffers.id) as saved_count'))
              -&gt;leftjoin('savedoffers', 'offers.id', '=', 'savedoffers.offer_id')
              -&gt;leftjoin('usedoffers', 'offers.id', '=', 'usedoffers.offer_id')
              -&gt;orderBy('offers.date', 'asc')
              -&gt;groupBy('offers.id')
              -&gt;get();
  </code></pre>
  
  <p>This still doesn't work as it seems to count both :/</p>
  
  <p>Much help is appreciated as feel like im banging my head against a wall!</p>