How to not select fields in Many to One relations

Question

In a Symfony 4 project I have an entity named rendezvous linked by a ManytoOne relation to other entities. One of these entities is user and I don't want to send the user's data with the result. How can I select all the fields excepted the user field with the doctrine query builder ? Is it better to put the relation in the user entity and not in the rendezvous entity.

Here are is my entity :

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass="App\Repository\RendezvousRepository")
 * @ORM\Table("rendezvous")
 */
class Rendezvous
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\User")
     */
    private $user;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Immeuble")
     */
    private $immeuble;

    /**
     * @ORM\Column(type="datetime")
     */
    private $creation_date;
}

And here is my query builder that return all the rendezvous :

public function findAllByUser($user){
      $qb = $this->createQueryBuilder('rdv');

      $qb
        ->where('rdv.user = :user')
        ->setParameter('user', $user)
      ;
      return $qb->getQuery()->getResult();
    }

Edit : I got the result I wanted to with the following DQL query :

public function findAllByUser($user){
  $query = $this->_em->createQuery('SELECT rdv.id, i, rdv.creation_date FROM App:Rendezvous rdv JOIN App:Immeuble i WITH rdv.immeuble=i JOIN App:User u WITH u=:user');
  $query->setParameter('user', $user);
  return $query->getResult();
}

ehymel ehymel · Accepted Answer · 2019-07-15T17:55:41

You can use a select statement to specify what you want:

public function findAllByUser($user) {
  $qb = $this->createQueryBuilder('rdv');

  $qb
    ->select('rdv.id')
    ->addSelect('rdv.immeuble')
    ->addSelect('rdv.creation_date')
    ->where('rdv.user = :user')
    ->setParameter('user', $user)
  ;
  return $qb->getQuery()->getResult();
}

Basically the select statement will overwrite any previously selected items so you can pull exactly the fields you want (as opposed to addSelect which does not override previous calls to select). Documentation.

If you want only specific information about user, you can do this:

public function findAllByUser($user) {
  $qb = $this->createQueryBuilder('rdv');

  $qb
    ->select('rdv.id')
    ->addSelect('rdv.immeuble')
    ->addSelect('rdv.creation_date')
    ->join('rdv.user', 'u')
    ->addSelect('u.username')
    ->where('rdv.user = :user')
    ->setParameter('user', $user)
  ;
  return $qb->getQuery()->getResult();
}

How to not select fields in Many to One relations

1 Answers