Doctrine doing too much querys

Question

I have multiple Entities and when I'm doing a findAll I have only 1 request excepted on one entity

/**
 * @ORM\Entity(repositoryClass="App\Repository\PropertyRepository")
 * @ORM\Table(name="property")
 */
class Property
{
/**
 * @ORM\Id()
 * @ORM\GeneratedValue()
 * @ORM\Column(type="integer")
 * @Assert\Type("integer")
 * @var int
 */
private $id;

/**
 * @ORM\Column(type="string", length=255)
 * @Assert\NotBlank
 * @Assert\Choice({"Appartement", "Maison", "Garage", "Bureau", "Château", "Commerce"})
 * @var string
 */
private $propertyCategory;

/**
 * @ORM\Column(type="string", length=255)
 * @Assert\NotBlank
 * @Assert\Type("string")
 * @var string
 */
private $uniqueName;

/**
 * @ORM\Column(type="string", length=255)
 * @Assert\NotBlank
 * @Assert\Type("string")
 * @var string
 */
private $address;

/**
 * @ORM\Column(type="string", length=255)
 * @Assert\NotBlank
 * @Assert\Type("string")
 * @var string
 */
private $city;

/**
 * @ORM\Column(type="integer")
 * @Assert\NotBlank
 * @Assert\Type("integer")
 * @Assert\Length(min = 5, minMessage = "Ce champ doit contenir 5 chiffres")
 * @Assert\Length(max = 5, maxMessage = "Ce champ doit contenir 5 chiffres")
 * @var int
 */
private $zipCode;

/**
 * @ORM\Column(type="string", length=255)
 * @Assert\NotBlank
 * @Assert\Country
 * @var string
 */
private $country;

/**
 * @ORM\Column(type="integer")
 * @Assert\NotBlank
 * @Assert\Type("integer")
 * @var string
 */
private $surfaceInSquareMeter;

/**
 * @ORM\Column(type="integer")
 * @Assert\NotBlank
 * @Assert\Type("integer")
 * @var int
 */
private $numberOfPiece;

/**
 * @ORM\Column(type="text", nullable=true)
 * @Assert\Type("string")
 * @var string
 */
private $description;

/**
 * @ORM\Column(type="string", length=255)
 * @Assert\NotBlank
 * @Assert\Choice({"Meublé", "Non meublé"})
 * @var string
 */
private $rentalCategory;

/**
 * @ORM\Column(type="float")
 * @Assert\NotBlank
 * @Assert\Type("float")
 * @var float
 */
private $rentExcludingCharges;

/**
 * @ORM\Column(type="float")
 * @Assert\NotBlank
 * @Assert\Type("float")
 * @var float
 */
private $charges;

/**
 * @ORM\Column(type="float")
 * @Assert\NotBlank
 * @Assert\Type("float")
 * @var float
 */
private $purchasePrice;

/**
 * @ORM\ManyToOne(targetEntity="App\Entity\User", inversedBy="properties")
 * @JoinColumn(name="user_property_id", referencedColumnName="id", onDelete="CASCADE")
 */
private $userProperty;

/**
 * @ORM\ManyToMany(targetEntity="App\Entity\Equipment", mappedBy="equipment")
 */
private $equipment;

/**
 * @ORM\ManyToMany(targetEntity="App\Entity\Lessee", mappedBy="lessee")
 */
private $lessees;

/**
 * @ORM\Column(type="string", length=255, nullable=true)
 * @Assert\File(
 *     maxSize = "2000k",
 *     mimeTypes = {"application/pdf", "application/x-pdf"},
 *     mimeTypesMessage = "Choisisez un fichier PDF"
 * )
 */
private $pdfFile;

I don't show you getter/setters because it's over 500 lines but I generated them.

Doctrine is doing 1 query for each property. To resolve my problem I tried to do this :

/**
 * @param User $user
 * @return array
 */
public function findPropertyByUser(User $user): array
{
    $qb = $this->createQueryBuilder('p')
        ->andWhere('p.userProperty = :user')
        ->setParameter('user', $user)
        ->orderBy('p.id', 'ASC')
        ->getQuery();
    return $qb->execute();
}

but it doesn't change anything, I try to use the findBy too but it doesn't change anything. Can anyone tell me where is my problem from ?

EDIT: according to the comments, that's probably the n+1 problem,

I tried differents things but I didn't find the solution, here is my actual method :

/**
 * @param User $user
 * @return array
 */
public function findPropertyByUser(User $user): array
{
    $qb = $this->createQueryBuilder('p')
        ->andWhere('p.userProperty = :user')
        ->setParameter('user', $user)
        ->orderBy('p.id', 'ASC')
        ->innerJoin('p.userProperty', 'u')
        ->addSelect('u')
        ->getQuery();
    return $qb->execute();
}

Finally request are comming from my view

{% if properties is defined %}
            {% for property in properties %}

                    {% set emails = [property.userProperty.email] %}
                    {% for lessee in property.lessees.values %}
                        {% set emails = emails|merge([lessee.email]) %}
                    {% endfor %}

                    {% if app.user.email in emails %}
                        <tr>
                            <td>{{ property.propertyCategory }}</td>
                            <td>{{ property.uniqueName }}</td>
                            <td>{{ property.address }}</td>
                            <td>{{ property.city }}</td>
                            <td>{{ property.zipCode }}</td>
                            <td>{{ property.rentExcludingCharges }} €</td>
                            <td>{{ property.charges }} €</td>
                            <td>{{ property.purchasePrice }} €</td>
                            <td>
                                <a href="{{ path('property_show', {'id': property.id}) }}">Voir</a>
                                {% if is_granted('ROLE_USER') %}
                                    <a href="{{ path('property_edit', {'id': property.id}) }}"><i class="fas fa-edit"></i> édition</a>
                                {% endif %}
                            </td>
                        </tr>
                    {% endif %}
            {% endfor %}
        {% else %}
            <tr>
                <td colspan="16">Vous n'avez pas enregistré de propriétés</td>
            </tr>
        {% endif %}

Start of solution I have one request less:

/**
 * @param User $user
 * @return array
 */
public function findPropertyByUser(User $user): array
{
    $qb = $this->createQueryBuilder('p')
        ->andWhere('p.userProperty = :user')
        ->setParameter('user', $user)
        ->orderBy('p.id', 'ASC')
        ->innerJoin('p.lessees', 'l')
        ->getQuery();
    return $qb->execute();
}

Answer 1

You need a leftJoin like:

/**
 * @param User $user
 * @return array
 */
public function findPropertyByUser(User $user): array
{
    $qb = $this->createQueryBuilder('p')
        ->leftJoin('p.lessees', 'l')
        ->andWhere('p.userProperty = :user OR l.email = :userEmail')
        ->setParameter('user', $user)
        ->setParameter('userEmail', $user->getEmail())
        ->orderBy('p.id', 'ASC')
        ->getQuery();

    return $qb->execute();
}

and in the view delete everything about email :

{% if properties is defined %}
            {% for property in properties %}
                <tr>
                    <td>{{ property.propertyCategory }}</td>
                    <td>{{ property.uniqueName }}</td>
                    <td>{{ property.address }}</td>
                    <td>{{ property.city }}</td>
                    <td>{{ property.zipCode }}</td>
                    <td>{{ property.rentExcludingCharges }} €</td>
                    <td>{{ property.charges }} €</td>
                    <td>{{ property.purchasePrice }} €</td>
                    <td>
                        <a href="{{ path('property_show', {'id': property.id}) }}">Voir</a>

                        {% if is_granted('ROLE_USER') %}
                            <a href="{{ path('property_edit', {'id': property.id}) }}"><i class="fas fa-edit"></i> édition</a>
                        {% endif %}
                    </td>
                </tr>
            {% endfor %}
        {% else %}
            <tr>
                <td colspan="16">Vous n'avez pas enregistré de propriétés</td>
            </tr>
        {% endif %}