Skip to content

Instantly share code, notes, and snippets.

@bigcalm
Created February 6, 2013 14:38
Show Gist options
  • Save bigcalm/4722929 to your computer and use it in GitHub Desktop.
Save bigcalm/4722929 to your computer and use it in GitHub Desktop.
Using Symfony2, DQL and knplabs/knp-paginator-bundle - how to get around "Cannot count query which selects two FROM components, cannot make distinction"
<ul>
{% for project in pagination %}
<li>{{ project.name }} has {{ project.?? }} tasks</li>
{% endfor %}
</ul>
<?php
class DemoController {
function demoAction()
{
$query = $this->get('doctrine.orm.entity_manager')->createQuery('
SELECT p, count(t)
FROM AcmeDemoBundle:Task t JOIN AcmeDemoBundle:Project p
ORDER BY p.createdAt DESC');
/**
* @var \Knp\Component\Pager\Paginator $paginator
*/
$paginator = $this->container->get('knp_paginator');
$pagination = $paginator->paginate(
$query,
$this->container->get('request')->query->get('page', 1)/*page number*/,
10/*limit per page*/
);
return $this->render('AcmeDemoBundle::demo.html.twig',
array('pagination' => $pagination)
);
}
}
<ul>
{% for line in pagination %}
{% set project = line[0] %}
{% set taskCount = line[1] %}
<li>{{ project.name }} has {{ taskCount }} tasks</li>
{% endfor %}
</ul>
<?php
class DemoController {
function demoAction()
{
$query = $this->em->createQuery('
SELECT p, (SELECT COUNT(t) FROM AcmeDemoBundle:Task t WHERE t.project = p)
FROM AcmeDemoBundle:Project p
ORDER BY p.createdAt DESC');
/**
* @var \Knp\Component\Pager\Paginator $paginator
*/
$paginator = $this->container->get('knp_paginator');
$pagination = $paginator->paginate(
$query,
$this->container->get('request')->query->get('page', 1)/*page number*/,
10/*limit per page*/
);
return $this->render('AcmeDemoBundle::demo.html.twig',
array('pagination' => $pagination)
);
}
@EnchanterIO
Copy link

@bigcalm thank you for your gist! It reminded me my first idea which was sub select. I was struggling with the LEFT JOIN issue and I replaced it with subselect. If it's not raining at least is dripping no?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment