SQL / Doctrine : Left Join problem

Question

Currently this function works : it displays for a specific game, how many jobs there are.

The problem : If there is no job, the game does not appear on the list.

How to display the game even if there is no job attached ?

Thanks

public function getWithGames()
    {
        $q = $this->createQuery('c')
            ->leftJoin('c.stJob j')
            ->where('j.expires_at > ?', date('Y-m-d h:i:s', time()))
            ->addOrderBy('c.name');

        $q->andWhere('j.is_activated = ?', 1);
        $q->andWhere('j.is_public = ?', 1);


        return $q->execute();
    }

Answer 1

Your conditions should be part of the LEFT JOIN ... ON clause.

$q = $this->createQuery('c')
    ->leftJoin('c.stJob j WITH j.expires_at > ? AND j.is_activated = 1 AND j.is_public = 1', date('Y-m-d h:i:s', time()))
    ->addOrderBy('c.name');

Putting conditions in the ON clause (as opposed to the WHERE) indicates that they apply specifically to the JOIN. If no rows satisfy those conditions, there is no join — and that's just what you want in this case. Putting them in the WHERE indicates that the result rows must satisfy those conditions. And obviously if there was no join, you can't satisfy any conditions about the j table.

Answer 2

$q->andWhere('j.is_activated = ?', 1);
$q->andWhere('j.is_public = ?', 1);

if there is no job its is_activated and is_public field cant be 1. it will be null so you need something like: where (j.is_activated = 1 and j.is_activated = 1) or (j.is_activated = IS NULL and j.is_activated IS NULL) ...

i am not familiar with what librar you are using to construct this query but i guess it could work like this:

public function getWithGames()
    {
        $q = $this->createQuery('c')
            ->leftJoin('c.stJob j')
            ->where('j.expires_at > ?', date('Y-m-d h:i:s', time()))
            ->addOrderBy('c.name');

        $q->andWhere('(j.is_activated = ? AND j.is_public = 1) OR (j.is_activated IS NULL  AND j.is_publicIS NULL )', 1);

        return $q->execute();
    }

this is not pretty... but as i said i dont know your library. you could probably pass an array of arguments or do something like "orWhere...." blabla...

the way it is done is also clumsy and could be done better. use the conditions directly to establish the join! that way you avoid ahving the whole tables combined and afterwards filtered...