Problems with orx doctrine

Question

I have something like:

$languages = ["English", "German", "Spanish"];

The Job Entity:

/**
 * @Flow\Entity
 */
class Job {
    /**
     * The name of first language for the job (M:1 unidirectional)
     *
     * @var \PATH\Language
     * @ORM\ManyToOne
     */
    protected $language1;

    /**
     * The name of second language for the job (M:1 unidirectional)
     *
     * @var \PATH\Language
     * @ORM\ManyToOne
     */
    protected $language2;
}

And Language Entity:

/**
 * @Flow\Entity
 */
class Language
{

    /**
     * The language name
     *
     * @var string
     * @Flow\Identity
     * @Flow\Validate(type="Text")
     * @Flow\Validate(type="NotEmpty")
     * @Flow\Validate(type="StringLength", options={ "minimum"=1, "maximum"=80})
     * @ORM\Column(length=80)
     */
    protected $name;
}

How can I get all jobs with language1 OR language2 in $languages? I tried the following, but it does not work...

I get empty results back.

$queryBuilder
            ->resetDQLParts()
            ->select("job")
            ->from("Job", "job")
            ->andWhere(
                $queryBuilder->expr()->orX(
                        $queryBuilder
                            ->innerJoin('job.language1', 'language1')
                            ->andWhere($queryBuilder->expr()->in("language1.name", $languages)),
                        $queryBuilder
                            ->innerJoin('job.language2', 'language2')
                            ->andWhere($queryBuilder->expr()->in("language2.name", $languages))
                )
            );

Any ideas?

Answer 1

I don't fully understand the query you have written, it's certainly not how I've used the QueryBuilder in the past. I've rewritten it how I'd use it.

The language fields do not state which entity they reference it should be:

/** @ORM\ManyToOne(targetEntity='AppBundle\Entity\Language')

Assuming a table structure of:

ID | Job Name | Language1 | Language2
1  | Job1     | French    | English
2  | Job2     | English   | Spanish

The SQL would be:

SELECT * FROM `jobs` 
     WHERE `language1` IN ("English", "French") 
     OR    `language2` IN ("English", "French");

Which translates to:

$this->createQueryBuilder('job')            
        ->where('job.language1 IN (:languages)')
        ->orWhere('job.language2 IN (:languages)')
        ->setParameter("languages", $languages)
        ->getQuery()
        ->getResult();

However with a table structure of:

-- Jobs
ID | Job Name | Language1 | Language2
1  | Job1     | 1         | 2
2  | Job2     | 2         | 3

-- Languages
ID | Language
1  | French
2  | English
3  | Spanish

You'd need something like this:

$this->createQueryBuilder('job')
        ->leftJoin('job.language1', 'language1')
        ->leftJoin('job.language2', 'language2')
        ->where('language1.language IN (:languages)')
        ->orWhere('language2.language IN (:languages)')
        ->setParameter("languages", $languages)
        ->getQuery()
        ->getResult();

Answer 2

I had a different problem too, that's why Dougs solution was not working for me. After fixing that problem and the help of his solution I came up with this:

    $queryBuilder
        ->leftJoin('Path\Language', 'language1', JOIN::LEFT_JOIN,  "job.language1 = language1")
        ->leftJoin('Path\Language', 'language2', JOIN::LEFT_JOIN,  "job.language2 = language2")
        ->andWhere(
            $queryBuilder->expr()->orX(
                $queryBuilder->expr()->in("language1.name", ":languages"),
                $queryBuilder->expr()->in("language2.name", ":languages")
            )
        )->setParameter("languages", $languages);