phpdoctrine-ormtypo3-flow

Problems with orx doctrine


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?


Solution

  • 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();