CODEWITHSUNDEEP
symfonydoctrine-orm
Helenesh
Helenesh

Reputation: 4349

Doctrine: select a table that is not managed by doctrine

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.

Upvotes: 2

Views: 2836

Answers (2)

David Rojo
David Rojo

Reputation: 2404

It is not quite clear the example sql with the code you have provided, but if you have a relation defined in your entities, you can join them with a query builder just by telling the relation field of the entity, so I think this should work

return $this->createQueryBuilder('i')
    ->select('i')
    ->innerJoin('i.images', 'a')
    ->getQuery()
    ->getResult();

As you have defined already your relations in your entities, Doctrine knows how to join your tables, so you just have to specify the relation field name and the alias.

And always remember that you have to use the field name in your entity (normally cameCasedStyle), not the column name at your database tables (normally snake_cased_style).

Upvotes: 0

dbrumann
dbrumann

Reputation: 17166

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.

Upvotes: 6

Related Questions