代码之家 › 专栏 › 技术社区 › Tiero L.

orx学说的问题

typo3-flow doctrine-orm php

Tiero L. · 技术社区 · 7 年前

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

/**
 * @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;
}

和语言实体:

/**
 * @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;
}

我怎样才能在$languages中找到所有使用language1或language2的工作? 我尝试了以下方法,但不起作用。。。

$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))
                )
            );

2 回复 | 直到 7 年前

Doug 7 年前

我不完全理解您编写的查询,这肯定不是我过去使用QueryBuilder的方式。我已经重写了我将如何使用它。

语言字段没有说明它们引用的实体应该是:

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

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

SQL将是:

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

也就是说:

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

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

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

你需要这样的东西:

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

Tiero L. 7 年前

我也有一个不同的问题,这就是为什么Dougs的解决方案对我不起作用。在解决了这个问题并得到了他的解决方案的帮助后,我得出了以下结论:

    $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);