Adding custom query to paginator (KNP Paginator) in Symfony2?

Question

I have the following query , how to write these query in Symfony2

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

i need to implement the above query in a symfony way

 private function getList($query = null)
{
    $em = $this->getDoctrine()->getEntityManager();

    if(!$query)
    {
        $query = $em->createQueryBuilder()
            ->select('i')
            ->from('ItxBundle:InventoryStock', 'i')
            ->innerJoin('i.Product','p')
            ->getQuery();

    }
    $adapter = $this->get('knp_paginator.adapter');
    $adapter->setQuery($query);
    $adapter->setDistinct(TRUE);


    $paginator = new Paginator($adapter);
    $paginator->setCurrentPageNumber($this->get('request')->query->get('page', 1));
    $paginator->setItemCountPerPage($this->container->parameters['items_per_page']);
    $paginator->setPageRange($this->container->parameters['page_range']);
    return $paginator;
}

and in the view

{% if entity.quantity - entity.Usage < 0 %}
    0
{% else %}
    {{ entity.quantity - entity.Usage | number_format(0) }}
{% endif %}

I am using three tables here , relation given below

InventoryStock 1 - n InventoryUsage InventoryStock 1 - 1 Product

need to show the available stock (InventoryStock.Quanitity - sum(InventoryUsage.quanitity))

also need to implement sort based on Stock as well

Would be great if any one could help me out as this has been pulling my hair out for couple of days.

RobMasters · Answer 1 · 2012-10-16T10:57:35.713

1

I'd recommend using the WhiteOctoberPagerfantaBundle bundle for this. It allows you to create your own Query object as you normally would, and then pass this to the relevant Pagerfanta adapter. It looks as if you're using Doctrine, so for example...

    // at the top of the file:
    use Pagerfanta\Pagerfanta, 
        Pagerfanta\Adapter\DoctrineORMAdapter;


    // Build your query...
    /** @var $query \Doctrine\ORM\Query */
    //$query = ... 

    $currentPage = $this->getRequest()->get('page', 1);

    $pagerfanta = new Pagerfanta(new DoctrineORMAdapter($query));
    $pagerfanta
        ->setMaxPerPage($maxItems) // you'll need to specify this value
        ->setCurrentPage($currentPage)
    ;
    $results = $pagerfanta->getCurrentPageResults();

edited Oct 16 '12 at 10:57

answered Oct 16 '12 at 10:50

RobMasters

4,108
2
27
34

Remember, DQL does not contain every function of plain SQL http://stackoverflow.com/questions/12652034/how-can-i-order-by-null-in-dql/12654552#12654552 I think SUM is not supported – DarkLeafyGreen Oct 16 '12 at 11:00
is it possible to sort as well ? – Unni Oct 19 '12 at 04:40
Its showing "Fatal error: Class 'WhiteOctober\PagerfantaBundle\WhiteOctoberPagerfantaBundle' not found in /var/www/Safflower/app/AppKernel.php on line 34 " – Unni Oct 19 '12 at 04:49
That just sounds like the vendor bundle hasn't been installed properly. If you're using Symfony 2.0 make sure you've updated your deps file, executed `php bin/vendors install` and added it to your `autoload.php` as well. If you're using Symfony 2.1 make sure you've added the vendor to your `composer.json` and executed `php composer.phar update`. – RobMasters Oct 19 '12 at 08:07
yes, did that and it started working .. but I came across another problem which I posted here http://stackoverflow.com/questions/12973709/writing-raw-query-in-symfony2-and-passing-it-to-pagefanta-for-pagination – Unni Oct 19 '12 at 11:55

Adding custom query to paginator (KNP Paginator) in Symfony2?

1 Answers1