ZF2 Doctrine with Symfony < OR 空查询

Question

如何以与学说兼容的方式写下"在哪里"条件？

( ( `translations_es_google`.`translation_date` < `translations_masters`.`translation_date` ) OR ( `translations_es_google`.`translation_date` is null ) )

我尝试过的（未成功）是：

$query1
    ->andWhere('((t.translationDate < m.translationDate) OR (t.translationDate is NULL))');
$query1
    ->andWhere('(t.translationDate < m.translationDate) OR (t.translationDate is NULL)');

学说查询的表别名

• t表示表translations_es_google
• m表示表translations_masters

当我删除此" and Where"条件时，其余的QueryBuilder查询按预期执行。

我要执行的查询是

SELECT 
    `translations_masters`.`translation_key` , `translations_masters`.`text_domain` , `translations_masters`.`translation_date` 
FROM 
    `translations_masters` 
LEFT JOIN 
    `translations_es_google` ON ( ( `translations_masters`.`text_domain` = `translations_es_google`.`text_domain` ) AND ( `translations_masters`.`translation_key` =`translations_es_google`.`translation_key` ) ) 
WHERE 
    `translations_masters`.`language_iso` NOT LIKE 'es-Google' AND ( ( `translations_es_google`.`translation_date` < `translations_masters`.`translation_date` ) OR ( `translations_es_google`.`translation_date` is null ) ) 
GROUP BY 
    `translations_masters`.`translation_key` , `translations_masters`.`text_domain` 
ORDER BY 
    `translations_masters`.`translation_date` ASC 

按照要求的查询的QueryBuilder版本是：

$query1 = $this->entityManager
    ->createQueryBuilder()
    ->select('m.textDomain , m.translationKey , m.translationDate , m.translationDate AS translationMasterDate , t.translationDate')
    ->from(
        'AMDatabaseEntityTheVerseTranslations' . $explode1 . $explode2,
        't'
    )
    ->leftJoin(
        'AMDatabaseEntityTheVerseTranslationsMasters',
        'm',
        Join::WITH,
        '(m.textDomain = t.textDomain) AND (m.translationKey = t.translationKey)'
    )
    ->groupBy('m.textDomain , m.translationKey')
    ->orderBy(
        'm.translationDate',
        'ASC'
    )
    ->setMaxResults('1');
$query1
    ->andWhere(
        $query1->expr()
               ->notLike(
                   'm.languageIso',
                   ':languageIso'
               )
    )
    ->setParameter(
        'languageIso',
        $value
    );
$query1->andWhere(
    $query1->expr()->orX(
        't.translationDate < m.translationDate',
        't.translationDate IS NULL'
    )
);
$result1 = $query1->getQuery()
                ->getArrayResult();

$ query1-> getQuery（）的输出（） -> getsql（） is

SELECT 
    t0_.text_domain AS text_domain_0, t0_.translation_key AS translation_key_1, t0_.translation_date AS translation_date_2, t0_.translation_date AS translation_date_3, t1_.translation_date AS translation_date_4
FROM 
    thev1010_theverseoftheday.translations_es_google t1_ 
LEFT JOIN 
    thev1010_theverseoftheday.translations_masters t0_ ON ((t0_.text_domain = t1_.text_domain) AND (t0_.translation_key = t1_.translation_key)) 
WHERE 
    t0_.language_iso NOT LIKE ? AND (t1_.translation_date < t0_.translation_date OR t1_.translation_date IS NULL) 
GROUP BY 
    t0_.text_domain, t0_.translation_key 
ORDER BY 
    t0_.translation_date ASC 
LIMIT 1

• ？值是'es-google'

$ query1-> getQuery（） -> getDQL（）; is

的输出

SELECT 
    m.textDomain , m.translationKey , m.translationDate , m.translationDate AS translationMasterDate , t.translationDate 
FROM 
    AMDatabaseEntityTheVerseTranslationsEsGoogle t 
LEFT JOIN 
    AMDatabaseEntityTheVerseTranslationsMasters m WITH (m.textDomain = t.textDomain) AND (m.translationKey = t.translationKey) 
WHERE 
    m.languageIso NOT LIKE :languageIso AND (t.translationDate < m.translationDate OR t.translationDate IS NULL) 
GROUP BY 
    m.textDomain , m.translationKey 
ORDER BY 
    m.translationDate ASC

• ？值是'es-google'

Answer 1

orX表达式如何？

您可以这样尝试：

$query1->andWhere(
    $query1->expr()->orxX(
         't.translationDate < m.translationDate',
         't.translationDate IS NULL'
    )
);

orX使您能够添加多个条件，并由逗号分隔。您还可以将orX条件分开与其自身变量，并将其添加到andWhere条件的所有内容。