QueryBuilder setLocale()

[neo...@gmail.com]

Hi guys,

I have to retrieve all documents filtered by locale via QueryBuilder.

this is the scenario:

I have a Document translatable (translator="attribute") and this are locales defined in config.yml:

doctrine_phpcr:
    odm:
        locales:
          en: []
            it:
                - en

default locale is 'en'.

Currently I have 3 documents, two of which are translated in 'it' and one of this is not translated.

So when i try to fetch document filtered by locale 'it' I get 3 documents, also the document not translated.

This is the query:

$qb = $this->createQueryBuilder('v');
$qb->setLocale($locale)
    ->from()
    ->document('AppBundle\Document\Visit', 'v')
    ->end();
$qb->orderBy()->desc()->field('v.modifiedAt');

$results = $qb->getQuery()->execute();

How can I retrieve only documents with $locale setted via setLocale()?

[David Buchmann]

hi neoomy,

setLocale is to tell the query builder what language you prefer. it does
not prevent language fallback if there is no translation. for that, you
need to cheat a bit: find a field in your Visit document that is
translated, then add a WHERE criteria for the translation.

for example, lets assume there is a $title property that is translated:

WHERE phpcr_locale:it-title IS NOT NULL


for completeness: if you use child translations, the query would be:


INNER JOIN [nt:unstructured] AS c ON ISCHILDNODE(c, u)
...
WHERE
...
AND NAME(c) = 'phpcr_locale:$locale'

> --
> You received this message because you are subscribed to the Google
> Groups "symfony-cmf-devs" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to symfony-cmf-de...@googlegroups.com
> <mailto:symfony-cmf-de...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

--
Liip AG // Agile Web Development // T +41 43 500 39 80
CH-8005 Zurich // PGP 0xA581808B // www.liip.ch

[neo...@gmail.com]

Hello David,
thanks for the reply. Now I more clearly.
My question now is how to write a query, you can suggest how to write it?

[David Buchmann]

i wrote a query fragment in my answer. in querybuilder, it would look
like this i think:

$qb = $this->createQueryBuilder('v');
$qb->setLocale($locale)
->from()
->document('AppBundle\Document\Visit', 'v')
->end()

->where()
->fieldIsset('title')

->orderBy()->desc()->field('v.modifiedAt');

fieldIsset expands to query for the field in the requested language
only, if i read the code correctly. if this does not work, look at the
generated sql2 query in the debug toolbar.

[neo...@gmail.com]

Thank you! it's work now.
there are only some fix to your code:
- close where() with end()
- set the alias in fieldIsset()

$qb->setLocale($locale)
    ->from()
    ->document('AppBundle\Document\Visit', 'v')
    ->end()
    ->where()

    ->fieldIsset('v.title')
    ->end()

    ->orderBy()->desc()->field('v.modifiedAt');