Doctrine: select a table that is not managed by doctrine

Question

Using the Doctrine QueryBuilder, I want to execute a query which in native SQL looks like this:

`SELECT image FROM image i INNER JOIN about_images a ON i.id = a.image_id`;

The result in DQL is as follows:

ImageRepository.php:

return $this->createQueryBuilder('i')
        ->select('i')
        ->innerJoin('about_images', 'a', 'WITH', 'i.id = a.imageId')
        ->getQuery()
        ->getResult();

Where image is an entity, and about_images is a join table (the result of a @ManyToMany relationship). However, I get the error that about_images is not defined, which makes sense as it is not managed by doctrine.

AboutPage.php (i.e the entity where the join table is created)

  /**
     * @var Image[]|ArrayCollection
     *
     * @ORM\ManyToMany(targetEntity="App\Entity\Image", cascade={"persist", "remove"})
     * @ORM\JoinTable(name="about_images",
     *     joinColumns={@ORM\JoinColumn(name="about_id", referencedColumnName="id")},
     *     inverseJoinColumns={@ORM\JoinColumn(name="image_id", referencedColumnName="id", unique=true)})
   */
    private $images;

Fields from Image entity:

    /**
     * @var int
     *
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(type="string", length=255)
     */
    private $image;

    /**
     * @var File
     *
     * @Vich\UploadableField(mapping="collection_images", fileNameProperty="image")
     * @Assert\File(maxSize="150M", mimeTypes={"image/jpeg", "image/jpg", "image/png", "image/gif"},
     *     mimeTypesMessage="The type ({{ type }}) is invalid. Allowed image types are {{ types }}")
     */
    private $imageFile;

    /**
     * @var string
     *
     * @ORM\Column(type="string", length=255, nullable=true)
     */
    private $imageAlt;

    /**
     * @var DateTime
     *
     * @ORM\Column(type="datetime")
     */
    private $updatedAt;

    /**
     * @var string
     *
     * @ORM\Column(type="string", nullable=true)
     */
    private $alt;

How can I solve this problem? The results should be Image entities.

You can use a native query (SQL) and then use RSM to map the fields returned by the result to your entity. See: doctrine-project.org/projects/doctrine-orm/en/2.6/reference/… Feel free to follow up if the docs are unclear or you get stuck implementing this and I can write a more in depth answer. — dbrumann
@dbrumann The documentation is indeed not easy to follow and I could not find clear examples on the internet. I would appreciate some guidance. Let me know if you need extra information about my Image entity. Thank you. — Helenesh
Please, post your class App\Entity\Image (property "about_pages"). By the way, you can do this ... $entityManager->getRepository(AboutPage::class)->createQueryBuilder("a")->join("a.images", "i") — SilvioQ
@SilvioQ I have updated my question with the code you requested. There is no aboutPage property in my Image entity. The images are referenced via the join table, which is why I need to access that join table. — Helenesh
mmm ... Image::class knows nothing about AboutPage::class ... what about $entityManager->getRepository(AboutPage::class)->createQueryBuilder("a")->join("a.images", "i")->select("a,i") — SilvioQ

dbrumann dbrumann · Accepted Answer · 2019-01-31T16:13:58

You can write native SQL and then map the output to your entities using a ResultSetMapper.

For your example it could look something like this in your Repository class:

public function findImagesWithAboutImages()
{
    $sql = 'SELECT i.* FROM image i INNER JOIN about_images a ON i.id = a.image_id';
    $entityManager = $this->getEntityManager();

    $mappingBuilder = new ResultSetMappingBuilder($entityManager);
    $mappingBuilder->addRootEntityFromClassMetadata(Image::class, 'i');

    $query = $entityManager->createNativeQuery($sql, $mappingBuilder);
    // If you want to set parameters e.g. you have something like WHERE id = :id you can do it on this query object using setParameter()

    return $query->getResult();
}

If you want related data you will have to add it to the select clause with an alias and then use $mappingBuilder->addJoinedEntityFromClassMetadata() to assign these fields to the joined entity much like above with the root entity.

Your annotations in your entity already define how each field maps to a property and what type it has, so basically you should get an array of Image-entities with everything (but the related entities) loaded usable.

Doctrine: select a table that is not managed by doctrine

2 Answers