Doctrine DQL: how to get expression to inversed side

Question

I wish to fetch an entity where the inversed association does not exist (on a 1:1 association)

I get the error:

A single-valued association path expression to an inverse side is not supported in DQL queries. Use an explicit join instead.

Query:

$query = $this->getEntityManager()->createQuery("
                SELECT DISTINCT(p.date)
                FROM MainBundle:Price p
                WHERE p.emaPrice IS NULL
                ORDER BY p.date ASC
            ")
            ->setMaxResults(1);
        $date = $query->getOneOrNullResult();

I understand the error, but I'm really stuck on fixing it. I've read I should add an explicit JOIN, but I've added it and still no luck.

EDIT query with join:

$query = $this->getEntityManager()->createQuery("
        SELECT DISTINCT(p.date)
        FROM MainBundle:Price p
        JOIN MomentumBundle:EmaPrice ep
        WITH ep.id = p.emaPrice
        WHERE p.emaPrice IS NULL
        ORDER BY p.date ASC
    ")
    ->setMaxResults(1);
$date = $query->getOneOrNullResult();

Terminal

How does your query look like with the join ? – Dolora

@Dolora added join query – Terminal

Can you get it to work in native SQL ? – Dolora

I'm confused by your query. You join on ep.id = p.emaPrice but then have WHERE p.emaPrice IS NULL. That will never match anything even if the query worked. – Swee

in that case a LEFT JOIN is needed – Dolora

Answer 1

I would like to thank @Flip and @lifo for their great support in helping me resolve this:

$query = $this->getEntityManager()->createQuery("
        SELECT DISTINCT(p.date)
        FROM MainBundle:Price p
        LEFT JOIN MomentumBundle:EmaPrice ep
        WITH ep.price = p.id
        WHERE ep IS NULL
        ORDER BY p.date ASC
    ")
    ->setMaxResults(1);
$date = $query->getOneOrNullResult();

Terminal