Doctrine returns many-to-many collection in incorrect order

Vote count:

0

I'm starting to struggle with finding a solution for this online/offline so maybe someone has an answer or suggestion of best approach.

The problem is that a Question entity has a many-to-many relation tags which should return Tag entities in alphabetical order. The generated SQL is correct, but the collection which is returned doesn't follow the order.

I'm starting to lean towards using:

a) lifecycle events to call a method which will sort the tags collection.

b) on getTags() method call a method which will check if collection was sorted (for performance to avoid multiple sorts) and then sort the collection if it's the first time.

Does anyone know why collection is not sorted or which solution will be best?

Some info of code:

Generated SQL

SELECT
    q0_.id AS id0,
    q0_.question AS question1, 
    t1_.id AS id2, 
    t1_.name AS name3 
FROM question q0_ 
LEFT JOIN questions_tags q2_ ON q0_.id = q2_.question_id 
LEFT JOIN tag t1_ ON t1_.id = q2_.tag_id 
ORDER BY
    q0_.question ASC, 
    t1_.name ASC

Available data

INSERT INTO `question` (`id`,`question`) VALUES (1,'Who are you?');
INSERT INTO `questions_tags` (`question_id`,`tag_id`) VALUES (1,1);
INSERT INTO `questions_tags` (`question_id`,`tag_id`) VALUES (1,2);
INSERT INTO `questions_tags` (`question_id`,`tag_id`) VALUES (1,3);
INSERT INTO `tag` (`id`,`name`) VALUES (1,'personal');
INSERT INTO `tag` (`id`,`name`) VALUES (2,'jobs');
INSERT INTO `tag` (`id`,`name`) VALUES (3,'achievements');

Partial dump of getQuestions() method

Doctrine\ORM\PersistentCollection {#3131
  -owner: AppBundle\Entity\Question {#3118
    -id: 1
    -question: "Who are you?"
    -tags: Doctrine\ORM\PersistentCollection {#3131}
  }
  -coll: Doctrine\Common\Collections\ArrayCollection {#3127
    -_elements: array:3 [
      0 => AppBundle\Entity\Tag {#3121
        -id: 1
        -name: "personal"
      }
      1 => AppBundle\Entity\Tag {#3123
        -id: 2
        -name: "jobs"
      }
      2 => AppBundle\Entity\Tag {#3074
        -id: 3
        -name: "achievements"
      }
    ]
  }
}

Question entity

<?php

namespace AppBundle\Entity;

use AppBundle\Entity\Tag;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\ORM\Mapping as ORM;

/**
 * Question
 *
 * @ORM\Table(name="question")
 * @ORM\Entity(repositoryClass="AppBundle\Entity\QuestionRepository")
 */
class Question
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="question", type="text")
     */
    private $question;

    /**
     * @var ArrayCollection
     *
     * @ORM\ManyToMany(targetEntity="Tag", inversedBy="questions", cascade={"persist"})
     * @ORM\JoinTable(name="questions_tags")
     * @ORM\OrderBy({"name"="ASC"})
     */
    private $tags;

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

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

    /**
     * Set question
     *
     * @param string $question
     * @return Question
     */
    public function setQuestion($question)
    {
        $this->question = $question;

        return $this;
    }

    /**
     * Get question
     *
     * @return string
     */
    public function getQuestion()
    {
        return $this->question;
    }

    /**
     * Add tags
     *
     * @param \AppBundle\Entity\Tag $tags
     * @return Question
     */
    public function addTag(Tag $tags)
    {
        $this->tags[] = $tags;

        return $this;
    }

    /**
     * Remove tags
     *
     * @param \AppBundle\Entity\Tag $tags
     */
    public function removeTag(Tag $tags)
    {
        $this->tags->removeElement($tags);
    }

    /**
     * Get tags
     *
     * @return \Doctrine\Common\Collections\Collection
     */
    public function getTags()
    {
        return $this->tags;
    }
}

Tag entity

<?php

namespace AppBundle\Entity;

use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\ORM\Mapping as ORM;

/**
 * Tag
 *
 * @ORM\Table(name="tag")
 * @ORM\Entity(repositoryClass="AppBundle\Entity\TagRepository")
 */
class Tag
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=128)
     */
    private $name;

    /**
     * @var ArrayCollection
     *
     * @ORM\ManyToMany(targetEntity="Question", mappedBy="tags")
     */
    private $questions;

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

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

    /**
     * Set name
     *
     * @param string $name
     * @return Tag
     */
    public function setName($name)
    {
        $this->name = $name;

        return $this;
    }

    /**
     * Get name
     *
     * @return string
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * Add questions
     *
     * @param \AppBundle\Entity\Question $questions
     * @return Tag
     */
    public function addQuestion(\AppBundle\Entity\Question $questions)
    {
        $this->questions[] = $questions;

        return $this;
    }

    /**
     * Remove questions
     *
     * @param \AppBundle\Entity\Question $questions
     */
    public function removeQuestion(\AppBundle\Entity\Question $questions)
    {
        $this->questions->removeElement($questions);
    }

    /**
     * Get questions
     *
     * @return \Doctrine\Common\Collections\Collection
     */
    public function getQuestions()
    {
        return $this->questions;
    }

    public function __toString()
    {
        return $this->name;
    }
}

QuestionRepository

<?php

namespace AppBundle\Entity;

use Doctrine\ORM\EntityRepository;

class QuestionRepository extends EntityRepository
{
    /** @return Question[] */
    public function getQuestions()
    {
        return $this->createQueryBuilder('q')
            ->select(['q', 't'])
            ->leftJoin('q.tags', 't')
            ->orderBy('q.question', 'ASC')
            ->getQuery()
            ->execute()
        ;
    }
}

asked 11 secs ago

Andrew Marcinkevičius

318

Doctrine returns many-to-many collection in incorrect order