title: Laravel Query Builder count from join
body: <p>So in my database I have table called <code>website_tags</code>, which contains <code>id, title</code> and so on, and also I have table called <code>websites</code>, with similar construction. And also there is table called <code>assigned_tags</code>, which contains relation between tags and websites, so it contains relation <code>id, tag_id and website_id</code>.</p>
  
  <p>What I need is to join these tables with query, I need to get all the tags and count how many times these tags are used. So, for example website_tags contains following information:</p>
  
  <pre><code>1: men
  2: women
  </code></pre>
  
  <p>And assigned tags contains like id: tag_id: website_id</p>
  
  <pre><code>1: 1: 1
  2: 1: 2
  3: 2: 2
  </code></pre>
  
  <p>So I will get that tag 'men' is used in 2 websites and tag 'women' is used in 1. <strong>How should I build the query?</strong>
  For now I have:</p>
  
  <pre><code>DB::table('website_tags')
  -&gt;join('assigned_tags', 'website_tags.id', '=', 'assigned_tags.tag_id')
  -&gt;select('website_tags.id as id', 'website_tags.title as title', DB::raw("count(assigned_tags.tag_id) as count"))-
  &gt;get();
  </code></pre>
  
  <p>But this is wrong, this query just counts rows in assigned_tags.</p>