Doctrine: select rows that are related to another table

Question

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.

score 1 · Accepted Answer · answered Dec 27 '18 at 23:28

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

So every time I want to filter by diferentes filters I have to refresh it, right? — Agustin Castro, Jan 04 '19 at 13:11
Yes,at the moment you have to. Maybe in the future something changes at doctrine and it can identify different filters. — Jannes Botis, Jan 04 '19 at 13:45

Doctrine: select rows that are related to another table

1 Answers1

A word of caution