Symfony 3 - 5, Doctrine 2. Is it possible using Criteria to select data from related tables in one query?

Question

symfony

Hello!

There is a table of ads and a table of users who submitted these ads.
When displaying a list of ads, usernames must be shown.

That is, I want to execute a query like

SELECT adverts.title, adverts.body, users.name 
FROM adverts 
LEFT JOIN users ON users.id = adverts.user_id

Can't figure out if it's possible to do this with OneToMany and ManyToOne annotations using Criteria

I have this now:

Adverts Entity Fragment

/**
   * @var Users
   * @ORM\ManyToOne(targetEntity="Users", inversedBy="advertsByUserId")
   * @ORM\JoinColumn(name="user_id", referencedColumnName="id")
  */
  private $userObject;

Entity Fragment Users

/**
     * @var array of Adverts
     * @ORM\OneToMany(targetEntity="Adverts", mappedBy="userObject")
    */
    protected $advertsByUserId;

Controller Fragment

/**
 * 
 * @param string $sRegion = '' код региона латинскими буквами
 * @param string $sCity = ''   код города латинскими буквами
 * @return array
*/
private function _loadAdvList(string $sRegion = '', string $sCity = '', Request $oRequest) : array 
{
  $limit = $this->getParameter('app.records_per_page', 10);
  $repository = $this->getDoctrine()->getRepository('App:Adverts');
  
  $oCriteria = Criteria::create();
  $e = Criteria::expr();
  $oCriteria->where( $e->eq('isDeleted', 0) )
    ->andWhere( $e->eq('isHide', 0) )
    ->andWhere( $e->eq('isModerate', 1) )
    ->orderBy(['delta' => Criteria::DESC])
    ->setMaxResults($limit)
    ->setFirstResult(0);
  

  //тут ещё разные фильтры по полям Adverts устанавливаются,
    // думаю в контексте вопроса не принципиально, вырезал
  
  
  $aCollection = $repository->matching($oCriteria)->toArray();
  return $aCollection;
}

view fragment

<div class="name">
    {% if (item.userObject) %}
      {{ item.userObject.displayName }}
    {% else %}
      {{ item.id }}
    {% endif %}
  </div>

We end up
with 1 query to the adverts table and
10 SQL queries to the users table, one for each ad

I understand how this can be done in one query using queryBuilder, but is it possible to achieve a single query query using Criteria?

Answer 1

Criteriais just an opportunity to form some relatively complex condition for QueryBuilder, nothing more.
You need to decide what exactly you want to do:

• If you want to select data from related tables, then this can be done through QueryBuilder with hydration, for example, into an array. In this case, there will be exactly one request - the one that you run
  
• If you want to select entities , then the logic of these selections is determined UnitOfWorkand, in general, may either lead to 10 requests or not, depending on the contents of the identity map. That is, relatively speaking, if the related entities would already be in the identity map, there would be no resampling. Also, the selection would not occur if you accessed the identity field of the associated entity. However, you are requesting data from a related entity, and doing it separately for each entity, why do you expect Doctrine not to get into the database for this data?