title: Adding custom query to paginator (KNP Paginator) in Symfony2?
body: <p>I have the following query , how to write these query in <strong>Symfony2</strong></p>
  
  <pre><code>SELECT Inventory_Stock.id, Inventory_Stock.quantity, SUM(InventoryUsage.quantity)
         ,Inventory_Stock.quantity - SUM(InventoryUsage.quantity) AS Stock 
  
  FROM Inventory_Stock LEFT JOIN InventoryUsage ON Inventory_Stock.id = InventoryUsage.InventoryStock_id 
  
  WHERE Inventory_Stock.id = 26 OR
        Inventory_Stock.id = 27
  
  GROUP BY Inventory_Stock.id 
  ORDER BY Stock DESC
  </code></pre>
  
  <p>i need to implement the above query in a symfony way</p>
  
  <pre><code> private function getList($query = null)
  {
      $em = $this-&gt;getDoctrine()-&gt;getEntityManager();
  
      if(!$query)
      {
          $query = $em-&gt;createQueryBuilder()
              -&gt;select('i')
              -&gt;from('ItxBundle:InventoryStock', 'i')
              -&gt;innerJoin('i.Product','p')
              -&gt;getQuery();
  
      }
      $adapter = $this-&gt;get('knp_paginator.adapter');
      $adapter-&gt;setQuery($query);
      $adapter-&gt;setDistinct(TRUE);
  
  
      $paginator = new Paginator($adapter);
      $paginator-&gt;setCurrentPageNumber($this-&gt;get('request')-&gt;query-&gt;get('page', 1));
      $paginator-&gt;setItemCountPerPage($this-&gt;container-&gt;parameters['items_per_page']);
      $paginator-&gt;setPageRange($this-&gt;container-&gt;parameters['page_range']);
      return $paginator;
  }
  </code></pre>
  
  <p>and in the view</p>
  
  <pre><code>{% if entity.quantity - entity.Usage &lt; 0 %}
      0
  {% else %}
      {{ entity.quantity - entity.Usage | number_format(0) }}
  {% endif %}
  </code></pre>
  
  <p>I am using three tables here , relation given below</p>
  
  <p>InventoryStock 1 - n  InventoryUsage
  InventoryStock 1 - 1  Product</p>
  
  <p>need to show the available stock  (InventoryStock.Quanitity - sum(InventoryUsage.quanitity))</p>
  
  <p>also need to implement sort based on Stock as well</p>
  
  <p>Would be great if any one could help me out as this has been pulling my hair out for couple of days.</p>