Make Doctrine to add entities in original order

I have following code:

$em = $this->getDoctrine()->getManager();

$evaluation->getQuestions()->clear();

foreach ($questions_data as $data) {
    $id = (int) $data['id'];        

    if ($id > 0) {
        $question = $em->getRepository('MyBundle:Question')->find($id);

        if ($question)
            $evaluation->getQuestions()->add($question);
    }
}

$em->persist($evaluation);
$em->flush();

Here is $questions_data — array(['id' => 2], ['id' => 1], ['id' => 3]).
And here is how doctrine persist questions to database:

So, how to make doctrine to don’t sort questions?
Evaluation entity has ManyToMany relation with the Question entity, so ORDER BY couldn’t help, because table evaluations_questions was created automatically by Doctrine and don’t have field id.

Answer

When you flush newly persisted items in Doctrine, Doctrine must determine which order to commit them to the database using an internal function getCommitOrder. The purpose of that function is to ensure that an object’s dependencies are committed before the object itself is committed. This is done to comply with any foreign key constraints that might be set up. As you observed, a consequence of ordering data to commit like this is that you lose the ability to finely tune the order that items are committed – this isn’t necessarily a bad thing.

In SQL the only way you can order your results is by issuing a query with ORDER BY. If you choose not to specify a sorting method, you cannot expect the results to come back in any particular order. The PostgreSQL docs explain this:

If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on.

In other words, it shouldn’t matter what order the content is stored in your database.

In your problem, the order by which questions appear to a user is an issue. You can’t allow the questions to appear randomly in an evaluation – they must follow a preset order. Frankbeen touches on this in a comment, but the best solution would be to add a new field on the Evaluation that stores an array of the Questions in the proper order. The order can then be read when you present the evaluation to a user.

---

If you absolutely must order them in a specific order in your database, you should be able to just flush the new objects individually as they are persisted instead of scheduling them to be flushed together.

$em = $this->getDoctrine()->getManager();

$evaluation->getQuestions()->clear();

foreach ($questions_data as $data) {
    $id = (int) $data['id'];        

    if ($id > 0) {
        $question = $em->getRepository('MyBundle:Question')->find($id);

        if ($question) {
            $evaluation->getQuestions()->add($question);
            $em->persist($evaluation);
            $em->flush();
        }
    }
}

Please be aware, this will take much more time to complete and is a pretty poor solution to your problem.