CODEWITHSUNDEEP
doctrine
Agustin Castro
Agustin Castro

Reputation: 459

Doctrine: select rows that are related to another table

I have two tables: assignment and file that is related to assignment. I would like to get rows of assignment that are related to any row of file applying the where sentence.

$querybuilder = $this->createQueryBuilder('query');
$querybuilder
    ->select('assignment')
    ->from(AssignmentReadModel::class, 'assignment')
    ->innerJoin('assignment.files', 'files')
    ->where('files.name=:archivo')
    ->setParameter('archivo', 'practica');

$data = $querybuilder->getQuery()->getResult();
$files = $data[0]->files();
var_export($files->count());

With this query I'm getting all the files when it should only get only one file "practica". When I execute "$files->count()" doctrine is doing a extra query to get all the files.

Upvotes: 0

Views: 2020

Answers (1)

Jannes Botis
Jannes Botis

Reputation: 11242

In query, no columns of the files entity are selected, so this is not part of the result rows and thus the relation is not hydrated.

To get the filtered files, select also the files relation:

$querybuilder
->select('assignment')
->addSelect('files')

A word of caution

Once a relation has been hydrated, this hydrated collection is used for this entity for other queries(reference all result row doesn't fetch in object, unless you choose to

1) refresh the collection:

$em->refresh($assignment);

and also include refrech option on cascade operations of the relation definition:

@OneToMany(targetEntity="File", mappedBy="assignment", cascade={"refresh"})

or

2) when using query builder to set:

use Doctrine\ORM\Query;

$querybuilder->setHint(Query::HINT_REFRESH, true);    // <-- Tell the hydrator to refresh

Upvotes: 1

Related Questions