title: native sql to Doctrine_RawSql
body: <p>I have this SQL Query.</p>
  
  <pre><code>SELECT
      c.id, 
      c.name,
      c.slug,
      sc.id,
      sc.name,
      sc.slug,
      COUNT(bsc.id) AS business_count
  FROM
      fi_category c
  LEFT JOIN
      fi_subcategory sc ON c.id = sc.category_id AND (sc.deleted_at IS NULL)
  LEFT JOIN (
          fi_business b
      INNER JOIN
          fi_business_subcategory bsc ON b.id = bsc.business_id AND (bsc.deleted_at IS NULL)
      INNER JOIN
          fi_suburb su ON su.id = b.suburb_id AND su.city_id = 1
      ) ON sc.id = bsc.subcategory_id
  WHERE 
      (c.deleted_at IS NULL) 
  GROUP BY 
      c.id, sc.id
  </code></pre>
  
  <p>as this type of query is not supported by DQL. i want to use it with <code>Doctrine_RawSql</code></p>
  
  <p>i tried using it this way.</p>
  
  <pre><code>$q = new Doctrine_RawSql();
  $q-&gt;select('{c.name}, {c.slug}, {sc.name}, {sc.slug}, {COUNT(bsc.id) AS business_count}');
  $q-&gt;from('fi_category c LEFT JOIN fi_subcategory sc ON c.id = sc.category_id AND (sc.deleted_at IS NULL) LEFT JOIN (fi_business b INNER JOIN fi_business_subcategory bsc ON b.id = bsc.business_id AND (bsc.deleted_at IS NULL) INNER JOIN fi_suburb su ON su.id = b.suburb_id AND su.city_id = 1) ON sc.id = bsc.subcategory_id');
  $q-&gt;groupBy('GROUP BY c.id, sc.id');
  $q-&gt;addComponent('c', 'Model_Category c');
  $q-&gt;addComponent('sc', 'c.Subcategory sc');
  $q-&gt;addComponent('bsc', 'sc.BusinessSubcategory bsc');
  $q-&gt;addComponent('b', 'bsc.Business b');
  $q-&gt;addComponent('su', 'b.Suburb su');
  $q-&gt;execute();
  </code></pre>
  
  <p>this query is not working, which is the correct way of building <code>Doctrine_Rawsql</code> for the above SQL query?</p>
  
  <p>if incase if someone wants to check the tables here is the link to fiddle <a href="http://sqlfiddle.com/#!2/5adaa" rel="nofollow noreferrer">http://sqlfiddle.com/#!2/5adaa</a></p>
  
  <p><strong>UPDATE:</strong> this is continuation of this question, <a href="https://stackoverflow.com/questions/12418335/select-query-and-count-based-on-condition">select query and count based on condition</a>  if someone could help me build <code>Doctrine_Rawsql</code> by using any of the query from the solution provided by @MvG</p>