1

I have a table with all entries including all translations in multiple languages: How can I create pagination sort links on translated fields? (Cake 3.1.6)

Summary: This is not working, I can't sort by the translations this way:

$this->Paginator->sort('_translations.es.title', 'Spanish')

Long version:

  • I'm using a i18n table with translate behavior.
  • I'm listing all translations (multiple languages) combined in a single table.
  • And now I want to use pagination sort links on the translations (sort by language).
| Title ENGLISH    | Title SPANISH    | Title GERMAN    |  = pagination sort links
| ---------------- | ---------------- | --------------- |
| Christmas        | Navidad          | Weihnachten     |
| Spring           | Primavera        | Frühling        |
| ...

So here is my simplified test setup:

Table Articles has only one field title to be translated.
i18n table is default setup as described in the book.

Baked Table class /src/Model/Table/ArticlesTable.php, added Translate Behaviour:

public function initialize(array $config) {
  // ... default config (removed in this post to simplify code)
  $this->addBehavior('Translate', ['fields' => ['title']]);  // added this line
}

Baked Entity class /src/Model/Entity/Article.php, added TranslateTrait:

namespace App\Model\Entity;
use Cake\ORM\Behavior\Translate\TranslateTrait; // added this line
use Cake\ORM\Entity;
class Article extends Entity {
  protected $_accessible = [
    '*' => true,
    'id' => false,
  ];
  use TranslateTrait; // added this line
}

Baked Controller /src/Controller/ArticlesController.php, modified as below:

namespace App\Controller;
use App\Controller\AppController;
class ArticlesController extends AppController {
  public $paginate = [
    'sortWhitelist' => [  // Allow pagination sort on this fields:
      'title',
      '_translations.es.title',
      '_translations.de.title'
    ]
  ];
  public function index() {
    $query = $this->Articles->find('translations'); // Retrieve All Translations
    $this->set('articles', $this->paginate($query));
  }
}

Baked View /src/Template/Articles/index.ctp, modified/simplified:

<table>
  <tr>
    <th><?= $this->Paginator->sort('title', 'English') ?></th>
    <th><?= $this->Paginator->sort('_translations.es.title', 'Spanish') ?></th>
    <th><?= $this->Paginator->sort('_translations.de.title', 'German') ?></th>
  </tr>
<?php foreach ($articles as $article): ?>
  <tr>
    <td><?= h($article->title) ?></td>
    <td><?= h($article->_translations['es']->title) ?></td>
    <td><?= h($article->_translations['de']->title) ?></td>
  </tr>
<?php endforeach; ?>
</table>

The translations in the table are shown correctly, but it's not possible to sort by the translated fields. When I click on the pagination links for the translations I get the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column '_translations.es' in 'order clause'

SQL Query:
SELECT Articles.id AS `Articles__id`,
       Articles.title AS `Articles__title`,
       Articles.created AS `Articles__created`,
       Articles.modified AS `Articles__modified`
FROM articles Articles 
ORDER BY _translations.es asc LIMIT 20 OFFSET 0

In this similar question the format Posts_title_translation.content is used - I have no idea where this is coming from, but I also tried it this way (of course I also added the variants of the field names in the paginator whitelist):

$this->Paginator->sort('Articles_title_translation', 'Spanish')
$this->Paginator->sort('Articles_title_translation.es', 'Spanish')
$this->Paginator->sort('Articles_title_translation.content', 'Spanish')
$this->Paginator->sort('Articles_title_translation.es.content', 'Spanish')

None of them is working... (obviously)

How can I sort the table items by the i18n translations of the title field?

Community
  • 1
  • 1
Oops D'oh
  • 941
  • 1
  • 15
  • 34

1 Answers1

2

Sorting on translated fields requires joins

Generally you can only sort on fields that are joined into the main query!

Translated fields are only joined in with a non-default current locale

By default, translated fields are only joined in, in case the current locale (I18n::locale()) does not match the default locale (I18N::defaultLocale(), intl.default_locale), ie when there is actually the need to translate something.

Once changing the current locale to a non-default

I18n::locale('de');
$query = $this->Articles->find('translations');
// ...

the translate behavior will contain associations with the translated content, and this is where the TableAlias_field_translation alias stems from, the behavior creates hasOne associations for each translated field using that naming scheme.

These fields can then be used for pagination, however this will only join in one locale at a time!

Ensure that the proper fields are used by the paginator

Since the associations are not being always contained, you'll have to take appropriate measures to ensure that the paginator uses the correct fields depending on the locale. Something like this (note that this is just untested example code for illustration purposes)

public function index()
{
    $sort = $this->request->query('sort');
    if ($sort) {
        $fieldMap = [
            'Articles_title_translation.content' => 'Articles.title'
        ];
        if (
            isset($fieldMap[$sort]) &&
            $this->Articles->locale() ===
                $this->Articles->behaviors()->get('Translate')->config('defaultLocale')
        ) {
            $this->request->query['sort'] = $fieldMap[$sort];
        }
    }

    $query = $this->Articles->find('translations');
    $this->set('articles', $this->paginate($query));
}

which would map the field to sort from the translated field to the original field in case no translations are going to be contained.

Joining in and sorting all translations/languages of a field

The above applies to sorting on a single field, which may or may not be translated. Having all translations of a field available for sorting is out of the translate behaviors scope. While the behavior does load all translations, it does so by using a hasMany association, ie using a separate query, and therefore they can't be used for sorting. Joining in all the translations needs to be done manually.

This may be something for a feature request, I'm not sure though if this is a common use case that would justify such core modifications, you may want to open an issue on GitHub or ask on IRC.

That being said, here's a basic example, an extended translate behavior that pretty much does what TranslateBehavior::setupFieldAssociations() and TranslateBehavior::beforeFind() do, just slightly modified. The behavior takes a locales option that needs to be fed with all locales that should be joined in, as they cannot be figured automatically.

src/Model/Table/ArticlesTable.php

// Remove $this->addBehavior('Translate', ['fields' => ['title']]);
// and load the custom behavior instead (otherwise there will be an
// error about "duplicate translation finders"

$this->addBehavior('MyTranslate', [
    'fields' => ['title'],
    'locales' => ['es', 'de']
]);

src/Model/Behavior/MyTranslateBehavior.php

namespace App\Model\Behavior;

use Cake\ORM\Behavior\TranslateBehavior;
use Cake\ORM\Query;
use Cake\ORM\Table;

class MyTranslateBehavior extends TranslateBehavior
{
    protected $_associations = [];

    public function __construct(Table $table, array $config)
    {
        $config += [
            'locales' => []
        ];

        parent::__construct($table, $config);
    }

    public function setupFieldAssociations($fields, $table, $model, $strategy)
    {
        parent::setupFieldAssociations($fields, $table, $model, $strategy);

        $alias = $this->_table->alias();
        $tableLocator = $this->tableLocator();
        $locales = $this->config('locales');

        $this->_associations = [];
        foreach ($fields as $field) {
            foreach ($locales as $locale) {
                $name = $alias . '_' . $field . '_translation_' . $locale;

                if (!$tableLocator->exists($name)) {
                    $fieldTable = $tableLocator->get($name, [
                        'className' => $table,
                        'alias' => $name,
                        'table' => $this->_translationTable->table()
                    ]);
                } else {
                    $fieldTable = $tableLocator->get($name);
                }

                $conditions = [
                    $name . '.locale' => $locale,
                    $name . '.model' => $model,
                    $name . '.field' => $field
                ];

                $this->_table->hasOne($name, [
                    'targetTable' => $fieldTable,
                    'foreignKey' => 'foreign_key',
                    'joinType' => 'LEFT',
                    'conditions' => $conditions,
                    'propertyName' => $field . '_translation_' . $locale
                ]);

                $this->_associations[] = $name;
            }
        }
    }

    public function findTranslations(Query $query, array $options)
    {
        $query->contain($this->_associations);
        return parent::findTranslations($query, $options);
    }
}

What this does should be relatively easy to understand, it will simply create and contain hasOne associations for all fields in all configured locales. The aliases will use the format TableAlias_field_translation_locale, like Articles_title_translation_es, which is what then needs to be used in the sort whitelist, and the paginator sort links.

It should be noted that a default sort order will probably be required with the new fields joined in, as it may otherwise sort the subquery used in querying the translations differently than the main query, leading to the wrong translations being retrieved! 

public $paginate = [
    'order' => ['Articles.title' => 'ASC']
    'sortWhitelist' => [
        'Articles.title',
        'Articles_title_translation_de.content',
        'Articles_title_translation_es.content'
    ]
];

$this->Paginator->sort('Articles.title', 'English');
$this->Paginator->sort('Articles_title_translation_es.content', 'Spanish');
$this->Paginator->sort('Articles_title_translation_de.content', 'German');

See also

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ndm
  • 59,784
  • 9
  • 71
  • 110