Symfony queryBuilder: too many queries

Question

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?

Impassable

Have you implemented the __toString() method in your User class ? this method would return the user name – Kenyon

I have, that returns $this->username. Without that it wouldn't display the usernames in the dropdown – Impassable

Did you try to add 'choice_label' => 'username' in the form builder options array? – Cambell

@MichaelSivolobov tried now, didn't work :( – Impassable

Lookup how to create a custom dql query. That will allows you to load the related users in one gulp. And read up a bit on lazy loading vs eager loading. Won't get very far with the orm unless you understand these key concepts. – Salmagundi

Are you sure it's the User entity being queried 500 times? My guess is it's something the User-Entity has a relation to instead. – Bedroom

[it makes a query for each user, to get the username] shouldn't, the EntityType execute this query just one time, the username is retrieved for each iteration of the result. github.com/symfony/symfony/blob/… – Pinnacle

@ccKep you're right, it's selecting all the fields from the user_profile and user_settings tables, which are OneToOne related to the users table. – Impassable

..even if I set them LAZY or EXTRA_LAZY @ORM\OneToOne(targetEntity="UserProfile", mappedBy="user", fetch="EXTRA_LAZY") – Impassable

Doctrine always retrieves OneToOne relations no matter what. You can't put a LAZY or EXTRA_LAZY setting to change that. But, you can join them as well in your QueryBuilder. see my answer for what should work. – Oratorio

@Impassable then adds the userProfile join statement to the query builder. – Pinnacle

Answer 1

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
])

Oratorio

You're a genius :) Btw it worked after removing the ->join('u.allowedUsers', 'au') line, because that relation doesn't exist (the allowed users are related to the offer, not to the user) – Impassable

Gotcha. Yeah the OneToOne relation thing tripped me up for a long time, and in one extreme case I changed the ORM schema to think it's a OneToMany, but through code still treated it like a OneToOne. Either way joins are your friend :) – Oratorio

Answer 2

If you don't have queries to your "entity" that don't need to fetch allowedUsers the simplest way would be to explicitly define fetch mode as EAGER in the field's annotations:

/**
 * @ORM\ManyToMany(targetEntity="User", fetch="EAGER")
 * @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;

Cambell

Done, but nothing changed – Impassable