Symfony doctrine specify fields in ManyToOne to improve performance?

Question

For example i have a simple entity "Cars" with a ManyToOne relation "Brands".

class Brands
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="string", nullable=false)
     */
    private $name;

    /**
     * @ORM\Column(type="string", nullable=false)
     */
    private $country;
}

public function getCountry(): ?string
{
    return $this->country;
}

Cars entity :

class Cars
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="string", nullable=false)
     */
    private $name;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Brands")
     */
    private $brand;
}


public function getBrand(): ?Brands
{
    return $this->brand;
}

public function getBrandCountry(): ?string
{
    return $this->getBrand()->getCountry();
}

If on my template i do {{ car.getBrandCountry}} (or {{ car.brand.name}} on template), the request executed on database will do :

SELECT t0.id, t0.name, t0.field3, t0.field4, t0.field5 [..........] FROM brands t0 WHERE t0.id = ?

But i only need "name" or "country" field from Brands, not others. On my list i have 500 lines to display and Symfony did 500 SELECT * only for load 2 fields that is very bad for performances.

How i can do to obtain something like that (to get only fields i need, not all fields.) :

SELECT name, country FROM brands WHERE id = 'xx' 

Thanks for your help

Answer 1

There are three solutions to your problem :

1. You build a first query which will collect the content of the brands table.
   With doctrine cache, when you will query the cars, doctrine won't query the brands table again as it have it in it's cache.

2. You query your cars table, collect the brand ids from the result and then query your brands table with the ids you collected. In this case, you will not do something like cars.brands.name in twig (else doctrine gonna make extra query). I would suggest to rebuild brands array result to make an associative one ([brand_id]=>[brand_data])

3. Make a partial query : https://www.doctrine-project.org/projects/doctrine-orm/en/2.11/reference/partial-objects.html

Answer 2

The best practices is to add a new function in Repository of your Entity, and use it after in your controller for ex in your case:

  public function getCustomDataById($brandId)
  {
       return $this->createQueryBuilder('b')
                ->select('b.name, b.country')
                ->where('b.id', $brandId)
                ->getQuery()
                ->getResult();
  }