title: Get Count Of Nested Entities using Laravel Eloquent
body: <p>I have got 3 database tables clients, coupons and categories</p>
  
  <pre><code>clients table
  </code></pre>
  
  <p>id,
  name,
  website,
  description,
  logo,
  slug</p>
  
  <pre><code>categories table
  </code></pre>
  
  <p>id,
  name,
  slug</p>
  
  <pre><code>coupons table
  </code></pre>
  
  <p>id,
  client_id,
  category_id,
  type,
  coupon,
  title,
  description,
  link,
  views,
  slug,
  expiry</p>
  
  <p>The relationship is 
  1) many coupons belongs to client ( many to one relationship)
  2) many coupons belongs to category ( many to one relationship)</p>
  
  <p>I am using laravel 5.1.</p>
  
  <p>How can i get the unique count of clients with the clients details , number of coupons a client has and the count of total categories an individual client has.</p>
  
  <p>simplified : i need to get the client details and display that xxx number of coupons are available in the xxx number of categories for a particular client.</p>
  
  <p>so far i can get the unique client details and the number of the coupons count.</p>
  
  <pre><code> public function getAvailableClientsWithItemCountList($page = 1)
  {
      return Client::join('coupons', 'clients.id', '=', 'coupons.client_id')
          -&gt;join('categories', 'coupons.category_id', '=', 'categories.id')
          -&gt;where('coupons.expiry', '&gt;', Carbon::today())
          -&gt;groupBy('clients.id')
          -&gt;skip(STORES_PER_REQUEST*($page-1))
          -&gt;take(STORES_PER_REQUEST)
          -&gt;get(['clients.id', 'clients.name', 'clients.slug', 'clients.logo', DB::raw('count(clients.id) as dealsCount'), DB::raw('count(categories.id) as categoriesCount')]);
  }
  </code></pre>
  
  <p>STORES_PER_REQUEST = 9 (constant) for paginating.
  thanks in advance.</p>