How to search on a relational field in Doctrine?

Question

PHP

Making a query to the database

$query = $records->createQueryBuilder('d')
                ->orderBy('d.id', 'ASC')
                ->where('d.version_id = :version')
                ->setParameter('version', $version)
                ->getQuery();

This raises an error:
Semantical Error] line 0, col 43 near 'version_id =': Error: Class App\Entity\Records has no field or association named version_id
But the table clearly has such a column

Also, a query without where that is built automatically uses version_id

$query = $records->createQueryBuilder('d')
                ->orderBy('d.id', 'ASC')
                ->getQuery();

SELECT r0_.id AS id_0, r0_.title AS title_1, r0_.link AS link_2, r0_.description AS description_3, r0_.visible AS visible_4, r0_.version_id AS version_id_5 FROM records r0_ ORDER BY r0_.id ASC

Other where fields work ok how to properly use with relation type field

Answer 1

When you work with an ORM and write queries with a query builder (or DQL), there is no such thing as a "column" for you. You are working with mapping tables to entities. Therefore, you have entity fields at your disposal. In the query builder, you are accessing the fields of an entity, not the columns of a table. To refer to the foreign key of an entity (for example, to make a condition without a relation join), the IDENTITY() function built into DQL is used. In your case it will look something like this:

$query = $records->createQueryBuilder('d')
                ->orderBy('d.id',  'ASC')
                ->where('IDENTITY(d.version) = :version')
                ->setParameter('version', $version)
                ->getQuery();