Can't get doctrines paginator working with Symfony

Question

Got 2 classes Item and Categoy, an item can have more categories and a category can have multiple items. Here is what the classes look like:

Item class:

<?php

namespace m\r\Entity;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="tblItems")
 */
class Item {
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @ORM\Column(type="string", length=200)
     */
    protected $title;

    /**
     * @ORM\ManyToMany(targetEntity="Category")
     * @ORM\JoinTable(name="Item_Category",
     *      joinColumns={@ORM\JoinColumn(name="Item_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="Category_id", referencedColumnName="id")}
     *      )
     */
    private $categories;

    public function __construct() {
        $this->categories = new \Doctrine\Common\Collections\ArrayCollection();
    }
    // getters and setters removed to keep post short
    // Add categories removed
    // Remove categories removed
}
?>

Category class:

<?php

namespace m\rBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="tblCategory")
 */
class Category {
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @ORM\Column(type="string", length=50)
     */
    protected $CategoryName;

    /**
     * @ORM\ManyToMany(targetEntity="Item")
     * @ORM\JoinTable(name="Item_Category",
     *      joinColumns={@ORM\JoinColumn(name="Category_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="Item_id", referencedColumnName="id")}
     *      )
     */
    private $items;

    public function __construct() {
        $this->items = new \Doctrine\Common\Collections\ArrayCollection();
    }
    // bunch of getters and setters removed to keep post short(and addCategory)
}
?>

The controller:

public function indexAction() {
    $dql = "select i, c FROM mrBundle:Item i  join i.categories c";
    $query = $this->getDoctrine()->getManager()->createQuery($dql)
            ->setFirstResult(0)
            ->setMaxResults(5);
    $paginator = new Paginator($query, $fetchJoinCollection = true);
    var_dump(count($paginator));// shows 2143 no matter what setMaxResults is
    return "Doest really work";
}

[UPDATE]

While posting this question I tried one more thing that seems to help:

    var_dump(count($paginator->getIterator()));

This gives 5 when setMaxResults is set to 5 and 500 when setMaxResults is set to 500. Have to check what exactly is returned and if I can access the categories of each item when showing them. Tried to do a var_dump on the paginator object for inspection but that crashes Firefox and brings down Fedora if I don't xkill it quick enough.

Answer 1

So, Based on what I read form the code,

if controller returns array('items' => $paginator),

you can use {% for item in items %} because paginator implements IteratorAggregate.

Next: When you use count($paginator) it will call count (implements \Countable).

This method does a real COUNT() using SQL, and behaves differently than count($paginator->getIterator()) which counts only the number of results).

One point, did you try to put fetchJoinCollection to false ? // shouldn't but anyway