How to make doctrine see fields in jointable?

Question

PHP

There are 2 entities. city ​​and service.
In Service:

/**
     * @var ArrayCollection
     * @ORM\ManyToMany(targetEntity="AppBundle\Entity\City", inversedBy="services")
     * @ORM\JoinTable(
     *     name="city_service_rel",
     *     joinColumns={@ORM\JoinColumn(name="service_id", referencedColumnName="id", onDelete="CASCADE")},
     *     inverseJoinColumns={@ORM\JoinColumn(name="city_id", referencedColumnName="id", onDelete="CASCADE")}
     * )
     */
    protected $cities;

In City:

/**
     * @var ArrayCollection
     * @ORM\ManyToMany(targetEntity="AppBundle\Entity\City", mappedBy="cities")
     */
    protected $services;

I'm trying to write a query through the query builder to get all the services that are in a certain city.

$qb = $this->createQueryBuilder('service');

        $qb
            ->where('service.active = true')
            ->andWhere($qb->expr()->isNotNull('service.representative'))
            ->innerJoin(
                'service.cities',
                'cities',
                'WITH',
                'cities.city_id = :id'
            )
            ->setParameter('id', $cityId)
        ;

As a result, the doctrine cannot find the related columns, and if service.cities is joined, it tries to find some properties in the City class.

Answer 1

Your mappings are already set up in such a way that you can simply get the desired city and get a collection of services that it contains through a getter - the doctrine will do everything for you.
As for the request, it is not entirely correct. Firstly, you substitute the parameter in the wrong place: the last parameter in innerJoin is responsible for which parameter to connect two entities. Secondly, you are accessing city_id when you are making a query not in SQL, but in DQL - that is, in fact, during the execution of this query, you are not attaching a table, but a collection of objects. At this level, doctrine is not aware of any city_id. Thirdly, when joining a collection, the doctrine understands that we are talking about many-to-many - accordingly, the last two parameters can be omitted when joining:

->join('service.cities', 'city')
->andWhere('city.id = :id')
->setParameter('id', $id)

When working with the doctrine, you should change your mindset a bit: you should abstract away from the database and think of entities as ordinary objects. I hope that I was able to correctly formulate the answer.