ZF2 Doctrine with Symfony

How do I write this "WHERE" condition in a way which is compatible with Doctrine?

如何以与Doctrine兼容的方式编写此“WHERE”条件?

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

What I have tried (unsuccessfully) are:

我尝试过的(失败的)是:

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

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

Table alias for the Doctrine query are

Doctrine查询的表别名是

t represents the table translations_es_google

t表示translations_es_google表

m represents the table translations_masters

m代表表translations_masters

When I remove this "andWhere" condition the rest of the QueryBuilder query executes as expected.

当我删除这个“andWhere”条件时,QueryBuilder查询的其余部分按预期执行。

The query I am trying to execute is

我试图执行的查询是

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

As requested the QueryBuilder version of this query is:

根据要求,此查询的QueryBuilder版本为:

$query1 = $this->entityManager
    ->createQueryBuilder()
    ->select('m.textDomain , m.translationKey , m.translationDate , m.translationDate AS translationMasterDate , t.translationDate')
    ->from(
        'AMDatabase\Entity\TheVerse\Translations' . $explode1 . $explode2,
        't'
    )
    ->leftJoin(
        'AMDatabase\Entity\TheVerse\TranslationsMasters',
        '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();

The output of $query1->getQuery()->getSQL() is

$ query1-> getQuery() - > getSQL()的输出是

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

The ? value is 'es-Google'

的?价值是'es-Google'

The output of $query1->getQuery()->getDQL(); is

$ query1-> getQuery() - > getDQL()的输出;是

SELECT 
    m.textDomain , m.translationKey , m.translationDate , m.translationDate AS translationMasterDate , t.translationDate 
FROM 
    AMDatabase\Entity\TheVerse\TranslationsEsGoogle t 
LEFT JOIN 
    AMDatabase\Entity\TheVerse\TranslationsMasters 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

The ? value is 'es-Google'

的?价值是'es-Google'

1 个解决方案

#1

How about orX expression?

orX表达怎么样?

You can try it this way:

你可以这样试试:

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

orX gives you the ability to add multiple conditions, separated by comma. You can also separate your orX condition to its own variable and pass everything added to it to andWhere condition.

orX使您能够添加多个条件,以逗号分隔。您还可以将orX条件分离为自己的变量,并将添加到其中的所有内容传递给条件。

#1