Symfony 4 как приджойнить?

144

14 октября 2021, 20:30

ошибка

In QueryException.php line 54:
  [Syntax Error] line 0, col 147: Error: Expected end of string, got 'ON'  

In QueryException.php line 43:
  SELECT eventListingImage FROM App\Entity\EventListingImage eventListingImage LEFT JOIN eventListingImage.newEventListingImage newEventListingImage ON newEventListingImage.eventListing = eventListingImage.eventListing WHERE newEventListingImage.eventListingImag  
  e IS NULL

запрос

        $c = $this->eventListingImageRepository->createQueryBuilder('eventListingImage')
            ->select('eventListingImage')
            ->leftJoin(
                'eventListingImage.newEventListingImage',
                'newEventListingImage',
                Join::ON,
                'newEventListingImage.eventListing = eventListingImage.eventListing'
            )
            ->andWhere('newEventListingImage.eventListingImage IS NULL')
            ->getQuery()
            ->getResult();

в \App\Entity\NewEventListingImage::$eventListingImage

    /**
     * @ORM\OneToOne(targetEntity="App\Entity\EventListingImage", mappedBy="newEventListingImage")
     *
     * @var EventListingImage|null
     */
    private $eventListingImage;

в \App\Entity\EventListingImage::$newEventListingImage

    /**
     * @ORM\OneToOne(targetEntity="App\Entity\NewEventListingImage", inversedBy="eventListingImage")
     * @JoinColumn(name="event_listing_id", referencedColumnName="event_listing_id")
     *
     * @var EventListing|null
     */
    private $newEventListingImage;

хочу получить запрос вида

select event_listing_image.*
from event_listing_image
         left join image i on event_listing_image.event_listing_id = i.event_listing_id
where i.id is null

если использовать

   ->leftJoin(
                'eventListingImage.newEventListingImage',
                'newEventListingImage'
            )

то ошибка

In QueryException.php line 215:
  A single-valued association path expression to an inverse side is not supported in DQL queries. Instead of "newEventListingImage.eventListingImage" use an explicit join.

Answer 1

Попробуйте это:

$c = $this->eventListingImageRepository->createQueryBuilder('eventListingImage')
            ->select('*')
            ->from('ваша таблица')
            ->leftJoin(
                'таблица к которой хотите подключится',
                'newEventListingImage',
                Join::WITH,
                'event_listing_image.event_listing_id = newEventListingImage.event_listing_id'
            )
            ->where('newEventListingImage.id IS NULL')
            ->getQuery()
            ->getResult();

Answer 2

через subquery получилось

        $subquery = $this->newEventListingImageRepository->createQueryBuilder('newEventListingImage')
            ->select('IDENTITY(newEventListingImage.eventListing)')
            ->andWhere('newEventListingImage.eventListing IS NOT NULL')
            ->getDQL();
        $qb = $this->eventListingImageRepository->createQueryBuilder('eventListingImage');
        $c = $qb
            ->select('eventListingImage')
            ->andWhere($qb->expr()->notIn('eventListingImage.eventListing', $subquery))
            ->getQuery()
            ->getResult();