CODEWITHSUNDEEP
symfonydoctrine-ormnullzend-framework2
Ron Piggott
Ron Piggott

Reputation: 705

ZF2 Doctrine with Symfony < OR null query

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

( ( `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

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

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:

$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

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 output of $query1->getQuery()->getDQL(); is

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

Upvotes: 0

Views: 127

Answers (1)

Artamiel
Artamiel

Reputation: 3762

How about orX expression?

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.

Upvotes: 1

Related Questions