Symfony 2.5 doctrine paginator does many selects fetching an relation

Question

in symfony2.5 with doctrine2.x i try to set a paginator as described on the doctrine documentation

I have a oneToOne relation between article and topten

/**
 * Article
 *
 * @ORM\Table(name="article", indexes={@ORM\Index(name="idx_title", columns={"title"})})
 * @ORM\Entity(repositoryClass="My\TestBundle\Entity\ArticleRepository")
 *{

class Article
{
    /**
     * @var integer
     *
     * @ORM\Column(type="bigint", name="id")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    // ...

     /**
     * @ORM\OneToOne(targetEntity="My\TestMobileBundle\Entity\Topten", mappedBy="article")
     */
     protected $topten;

     public function __construct() {
        $this->topten = new ArrayCollection();
    }
}

Topten Entity:

/**
 * Topten
 *
 * @ORM\Table(name="topten", indexes={@ORM\Index(name="article_id_idx", columns={"article_id"})})
 * @ORM\Entity(repositoryClass="My\TestMobileBundle\Entity\ToptenRepository")
 *
 */
class Topten
{
    /**
     * @var integer
     *
     * @ORM\Column(type="bigint", name="id")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

     //...

     /**
     * @ORM\OneToOne(targetEntity="My\TestBundle\Entity\Article", inversedBy="topten")
     * @ORM\JoinColumn(name="article_id", referencedColumnName="id", unique=true)
     */
     protected $article;

}

Controller:

        $dql = "SELECT a from MyTestBundle:Article a";
        $em = $this->getDoctrine()->getManager();

        $query = $em->createQuery($dql)
            ->setFirstResult(0)
            ->setMaxResults(20);

$articles = new Paginator($query, $fetchJoinCollection = true);

        foreach($articles as $article)
        {   
            echo $article->getTitle() . "<br/>\n";
        }
//...

Doctrine now does 21 Mysql Queries.

Everytime i print the title, it does the Query:

SELECT 
  t0.id AS id1, 
  //...
FROM 
  topten t0 
WHERE 
  t0.article_id = ?

What am I doing wrong or how can I stop this behavior?

Answer 1

I fixed it myself by adding setHint to the Query:

$query = $em->createQuery($dql)
        ->setHint(\Doctrine\ORM\Query::HINT_FORCE_PARTIAL_LOAD, true)
        ->setFirstResult(0)
        ->setMaxResults(20);

I found this answer by reading this answer here on stackoverflow Seems to be a common problem with one-to-one relations in doctrine.

Answer 2

This my paginator, maybe useful to someone:

namespace XXX\YourBundle\Library;

/**
 * @author websky
 * 
 * Licensed MIT
 * Paginator 
 * 
 * instruction:
 * 1. Controller
 * use XXX\YourBundle\Library\Paginator;
 * new Paginator(data_array, limit, $current);
 * and metods
 * createPaginator()
 * createPaginatorData()
 * 
 * example:
 * controller
 * use XXX\YourBundle\Library\Paginator;
 * $page = ($request->query->get('page') ? $request->query->get('page') : 1);
 * $paginator = new Paginator($this->resultQueryArray(), 1, $page);
 * return $this->render('YourBundle:Your:your.html.twig', array('list' => $paginator->createPaginator(), 'resultData' => $paginator->createPaginatorData()));
 * 
 * 2. View - example
 * {% if resultData.allElements >= 2 %}
 *           <section class="pagination">
 *               {% if resultData.first == resultData.current %}
 *                   <a href="#" class="firstoff"></a>
 *               {% else %}
 *                   <a href="{{path('your_route',{'page' : resultData.first})}}" class="first"></a>
 *               {% endif %}    
 *               {% if resultData.previous == 0 %}
 *                   <a href="#" class="prevoff"></a>
 *               {% else %}
 *                   <a href="{{path('your_route',{'page' : resultData.previous})}}" class="prev"></a>
 *               {% endif %}    
 *               {% for i in 1..resultData.allElements %}
 *                   {% if resultData.allElements < 8 %}
 *                       {% if i == resultData.current %}
 *                           <a href="{{path('your_route',{'page' : i})}}"  class="page-number active">{{ i }}</a>
 *                       {% else %} 
 *                           <a href="{{path('your_route',{'page' : i})}}"  class="page-number">{{ i }}</a>
 *                       {% endif %}
 *                   {% else %}
 *                       {% if i == resultData.current %}
 *                           <a href="{{path('your_route',{'page' : i})}}"  class="page-number active">{{ i }}</a>
 *                       {% elseif (i == (resultData.current - 3)) or (i == (resultData.current - 2)) or (i == (resultData.current - 1)) or (i == (resultData.current + 1)) or (i == (resultData.current + 2)) or (i == (resultData.current + 3)) %}
 *                          <a href="{{path('your_route',{'page' : i})}}"  class="page-number">{{ i }}</a>         
 *                       {% endif %}
 *                   {% endif %}
 *                   
 *               {% endfor %} 
 *               of <a href="{{path('your_route',{'page' : resultData.allElements})}}"  class="page-number">{{resultData.allElements}}</a>
 *               {% if resultData.next  %}      
 *                   <a href="#{{path('your_route',{'page' : resultData.next})}}"  class="next"></a>
 *               {% else %}
 *                   <a href="#" class="nextoff"></a>
 *               {% endif %}
 *               {% if resultData.last == resultData.current %} 
 *                   <a href="#" class="lastoff"></a>  
 *               {% else %}
 *                   <a href="{{path('your_route',{'page' : resultData.last})}}" class="last"></a>
 *               {% endif %}
 *           </section>
 *           {% endif %}
 */
class Paginator 
{
    protected $limit;
    protected $current;
    protected $previous;
    protected $next;
    protected $jump;
    protected $last;
    protected $data;
    protected $numberElements;
    protected $allElements;
    protected $sourceLength;

    /**
     * Pagination construct object
     * @param array $data
     * @param integer $limit
     * @param integer $current
     */
    public function __construct($data, $limit = 5, $current = 1)
    {
        $this->data = $data;
        $this->limit = $limit;
        $this->current = $current;
        $this->sourceLength = count($data);
        $this->setAllElements();
        $this->setNumberElements();
    }

    /**
     * Set limit
     * @param integer $limit
     * @return \XXX\YourBundle\Library\Paginator
     */
    public function setLimit($limit)
    {
        $this->limit = $limit;

        return $this;
    }

    /**
     * Get limit
     * @return integer
     */
    public function getLimit()
    {
        return $this->limit;
    }  

    /**
     * Set current
     * @param integer $current
     * @return \XXX\YourBundle\Library\Paginator
     */
    public function setCurrent($current)
    {
        $this->current = $current;

        return $this;
    }

    /**
     * Get current
     * @return integer
     */
    public function getCurrent()
    {
        return $this->current;
    }

    /**
     * Set previous
     * @param integer $previous
     * @return \XXX\YourBundle\Library\Paginator
     */
    public function setPrevious($previous)
    {
        $this->previous = $previous;

        return $this;
    }

    /**
     * Get previous
     * @return integer
     */
    public function getPrevious()
    {
        return $this->previous;
    }

    /**
     * Set next
     * @param integer $next
     * @return \XXX\YourBundle\Library\Paginator
     */
    public function setNext($next)
    {
        $this->next = $next;

        return $this;
    }

    /**
     * Get next
     * @return integer
     */
    public function getNext()
    {
        return $this->next;
    }

    /**
     * Set last
     * @param integer $last
     * @return \XXX\YourBundle\Library\Paginator
     */
    public function setLast($last)
    {
        $this->last = $last;

        return $this;
    }

    /**
     * Get last
     * @return integer
     */
    public function getLast()
    {
        return $this->last;
    }

    /**
     * Set AllElements
     * These are all elements of an array.
     * @return \XXX\YourBundle\Library\Paginator
     */
    public function setAllElements()
    {
        $this->allElements = count($this->data);
        return $this;
    }

    /**
     * Set NumberElements
     * This is the number of pages for the paginator.
     * @return \XXX\YourBundle\Library\Paginator
     */
    public function setNumberElements()
    {
        $this->numberElements = ceil ($this->allElements / $this->limit);
        return $this;
    }

    /**
     * Creates an array of data to control.
     * @return array
     */
    public function createPaginatorData()
    {
        $shareArray['limit'] = $this->limit;
        $shareArray['allElements'] = $this->numberElements;
        $shareArray['current'] = $this->current;
        $shareArray['first'] = 1;
        $shareArray['previous'] = $this->current != 0 ? $this->current - 1 : false;
        $shareArray['next'] = $this->current != $this->numberElements ? $this->current + 1 : false;
        $shareArray['last'] = $this->numberElements;
        $shareArray['sourcelength'] = $this->sourceLength;

        return $shareArray;
    }

    /**
     * Returns the elements of the page.
     * @return array
     */
    public function createPaginator()
    {
        $result = array();
        for($i=(($this->current * $this->limit) - $this->limit); $i < ($this->current * $this->limit); $i++){
            if(array_key_exists($i, $this->data)){
                $result[] = $this->data[$i];
            }
        } 
        return $result;
    }
}