Convert SQL into DQL (Doctrine2) with join table

Question

Similar questions exist, but i found none mentionning the silent join table problem.

I have a simple SQL query with a join but can't convert it to DQL, because of this join table. There are two entities, Shop and Client, linked by a ManyToMany association.

Doctrine2 silently made a join table: shop_client.

Now, if i want to get all clients that do not belong to a given shop i simlpy do:

SELECT c.name FROM `client` AS c
JOIN `shop_client` AS sc
    ON sc.client_id = c.id
    AND sc.shop_id != :shop_id

This is straight forward, really easy, but i cannot come up with the DQL version.

I tried to use "EXISTS", "NOT IN", "SIZE" but could never end up with a working DQL query.

Edit: My Shop entity has a clients field, but the Client entity has no shops field.

Answer 1

SELECT
    c
FROM
    Client c
WHERE
    c.id IN (
        SELECT
            c2.id
        FROM
            Shop s2
        JOIN
            s2.clients c2
        WHERE
            s2.id != :shopId
    )

Also, consider that building such queries without entity definitions is like shooting in the dark. Post them with the question next time.