[FIXED] Symfony queryBuilder: too many queries

Issue

I have an entity with a ManyToMany relationship with the User table:

/**
 * @ORM\ManyToMany(targetEntity="User")
 * @ORM\JoinTable(
 *  name="offer_allowedusers",
 *  joinColumns={
 *      @ORM\JoinColumn(name="offer_id", referencedColumnName="id", onDelete="CASCADE")
 *  },
 *  inverseJoinColumns={
 *      @ORM\JoinColumn(name="user_id", referencedColumnName="id", onDelete="CASCADE")
 *  }
 * )
 */
private $allowedUsers;

And, in the form, I want to display a dropdown (using select2) to select which users are allowed:

To do that, I made, in the Form building:

->add('allowedUsers', EntityType::class, [
        'class' => 'AppBundle\Entity\User',
        'query_builder' => function (EntityRepository $er) {
            return $er->createQueryBuilder('u')
            ->orderBy('u.username', 'ASC');
        },
        'label' => 'Allowed users',
        'required' => false,
        'multiple' => true
    ])

The problem is that it makes a query for each user, to get the username... So if I have 500 users, it makes 500 queries...

How can optimize and do a single query to fetch all the records?

Solution

Explicitly create the join in your QueryBuilder, and select both the user and allowed users.

UPDATE

You must also join and select your user profile and user settings OneToOne relations because Doctrine automatically retrieves OneToOne relations any time you fetch the User entity.

The Doctrine documentation talks about why it has to perform an extra query when fetching the inverse side of a one-to-one relation.

->add('allowedUsers', EntityType::class, [
    'class' => 'AppBundle\Entity\User',
    'query_builder' => function (EntityRepository $er) {
        return $er->createQueryBuilder('u')
            ->select('u, au, up, us')
            ->join('u.allowedUsers', 'au')
            ->join('u.userProfile', 'up')
            ->join('u.userSettings', 'us')
            ->orderBy('u.username', 'ASC')
        ;
    },
    'label' => 'Allowed users',
    'required' => false,
    'multiple' => true
])

Answered By - Jason Roman