I would like to sort ascending translated terms of taxonomy.
I couldn't find the field "Taxonomy term: Name (translated)" in the criteria sorting list.
Any solution or workaround?
Or is that a "feature request" issue?

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

rondev’s picture

Status: Active » Closed (won't fix)

It is an Internationalization Views related issue. And I found the following comment in code:
// TODO currently almost impossible, JOIN to i18n_string table needed
/*
'sort' => array(
//'handler' => 'views_handler_sort',
'handler' => 'i18nviews_handler_sort_taxonomy_term_name',
),
*/
:-(

rondev’s picture

Project: Views (for Drupal 7) » Internationalization Views
Version: 7.x-3.3 » 7.x-3.x-dev
Component: taxonomy data » Code
Status: Closed (won't fix) » Active

Change project issue list.

Can we hope this can be solved? Which is the limitation, Drupal core, Views?

Cyclodex’s picture

I am also interested and willing to help fix this issue.

I just fixed something similar for my exposed filters in views : #1312074: Sort exposed translated filters by their value.

liquidcms’s picture

until this gets fixed; a PHP Sort can do the trick quite nicely.

global $language;
$term1 = i18n_taxonomy_term_name(taxonomy_term_load($row1->tid), $language->language);
$term2 = i18n_taxonomy_term_name(taxonomy_term_load($row2->tid), $language->language);
if ($term1 > $term2) return 1;
else return -1;
filburt’s picture

Works perfectly - thanks!

Filburt

Anonymous’s picture

Views does come with the ability to display rendered taxonomy terms now (at least in 3.5, dunno when it was introduced). However, the sort handler for ordering by translated taxonomy terms is still missing. For those who find this thread (like me) and only wanna sort by translated taxonomy term (aka rendered), I have made a feature request here: http://drupal.org/node/1866000 - because I think this rather belongs into views. Correct me if I am wrong :-)

I hope this is moving forward a little, since all published work-arounds don't work for me.

Cheers,
Stephan

joel_osc’s picture

FYI - entity translation has gone beta recently and everything works in views much better - including sorting.

rondev’s picture

I just installed Entity Translation for ability of per exemple translating captions of media images. It works well and I'm very happy with that. Moreover it will probably be upgradable to D8 as it seems to be the future. I was thinking to change to Entity Translation for taxonomy too.
I will do on a test site. But I think at the moment there could be issues with xmlsitemap, ... I will give some news when it is ready.

Anonymous’s picture

Hm... I fail to see how Entity Translation helps with the described taxonomy term sorting problem. Isn't Entity Translation not just for ... entities?

joel_osc’s picture

Entity translation plus title module (which creates a replacement for the term name which is a field) means that a single tid will have a name_field for each language. So the term's name_field will be available to views in multiple languages. So a view showing name_field {not term name (translated)} will display two rows, one row for each language), add a filter for name_field (= user's current language) sort by name_field and your done. I think going forward entity translation is the way to go...it is much easier to manage translatable fields than two separate entities (node/term) and synchronizing common fields between them. Although, not all the corner cases have quite been worked out yet it Entity Translation is working quite well.

Anonymous’s picture

@joel_osc Thanks for that explanation, I appreciate it. Looks like I'll have to play with it a little to find the 'right way'.

jgullstr’s picture

Category: Support request » Feature request
Issue summary: View changes
Status: Active » Needs review
FileSize
3.49 KB

To be able to sort by translation, we need a join to the locales_target table, where the actual translations are stored. An intermediary join to i18n_string is needed to get the string ids. I've attached a handler that adds these joins and sorts by localized name.

As my experience writing views handlers is limited, I'm not sure that the use of "$this->table" is correct in this patch, or if some alias should be used. At the very least, this should be a good starting point for getting this issue fixed.

EDIT: Also, there is no fallback language in this patch, so terms lacking translations are not sorted at all. Should terms without translations be hidden or be "mixed in" sorted by default language? An option in the sort could be an idea?

jgullstr’s picture

FileSize
3.63 KB

For results not to go haywire when sorting using relationships, table alias needs to be used when available.

I'm not sure that I'm using the $base parameter correctly when adding the relationships:

$base: The name of the 'base' table this relationship represents; this tells the join search which path to attempt to use when finding the path to this relationship.

I'd be grateful if someone could give a more verbose explanation of this part, and what repercussions may arise from using it incorrectly.

Corrected patch attached.

Adirael’s picture

Patch in #13 worked great for me.

devad’s picture

Status: Needs review » Needs work

Patch in #13 works great for me as well (thanx), but for translated term names without relationship only (Do not use relationship).

It seems that if some relationship is selected filter doesn't apply.

I have a view with term-parent relationship grouped by parents.

I have added sort: (Parent) Taxonomy term: Name (translated) (asc)

But it sorts nothing.

Changing status to "needs work".

devad’s picture

Status: Needs work » Reviewed & tested by the community

Patch #13 works nice without relationship. So, maybe it's better to change status of this issue to "Reviewed & tested by community".

And "Sort by Taxonomy term: Name (translated) field WITH RELATIONSHIP" could be the following feature request.

Step by step...

jasom’s picture

Code in #13 worked for me! One small note: you need to clear all caches after applying this patch. if you want to see "Taxonomy term: Name (translated)" option in views_ui.

jasom’s picture

Solution in #13 still can be a little bit more perfect.
It sorts non-English characters at the end of regular-English character. For example "Š" (sharp S) should go after "S" not after "Z" (the last letter).

See the example here:

Transliteration module handle this for Pathauto and files uploaded to file system.

Special characters can be: Ř, Ľ, Č, Ž, Š, Ď, Ť, Ň...

jgullstr’s picture

@jasom,

As this patch sorts values in the database, the sort order is decided by the database collation, not the handler itself. By setting a proper collation, your list should get sorted correctly.

jasom’s picture

@jgullstr

Thanks for a hint. What do you mean by "By setting a proper collation"? Should I set something in database or in server?

jgullstr’s picture

A collation is a set of rules for comparing characters in a character set (source).

The database collation is set per table in the database. For a list of available collations, see here. In your case, utf8_czech_ci would probably be the correct one. For how to change your current collation, see here.

You should also alter your settings.php, so new tables are created using the correct collation. The collation can be defined in the settings variable $databases as follows (assuming you use the default database):

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      /*...*/
      'collation' => 'utf8_czech_ci',
    ),
  ),
);

IIRC, the preferred collation can also be set globally in your sql configuration, but you will still need to alter your existing databases.

Also, remember, at the very least, to backup your site before trying this. You are, after all, talking to a stranger on the internet :)

pixelpreview@gmail.com’s picture

for a multilanguage site, I choose utf8_general_ci
I have 3 languages on my site : nederlands, french and deutch

I have a view with a sort on translated term name but in french that have accentued characters like 'éèà...' the order is not correct and same problems with uppercases

I have this for example

DUNE
dame
durant
défendre
départ

au lieu de :

dame
défendre
départ
DUNE
durant

Any idea to resolve this problem ?

jgullstr’s picture

You could always use collate in the query, thus determine language at run-time. This could be a good feature to add to i18nviews, to allow different sorting rules per language.

In the meantime, Here's one possible solution.

pixelpreview@gmail.com’s picture

with your solution, my view renders no results. I see with a var_dump that the COLLATE is added to my order by

public 'orderby' => &
array (size=1)
0 => &
array (size=2)
'field' => string 'term_i18n_string_translation_translation COLLATE utf8_unicode_ci' (length=64)
'direction' => string 'ASC' (length=3)

but no results in any language I remove the collate and I have results :(

danon1981’s picture

I tried #13 but this gives me a Broken/missing handler when I add sort on translated term name

yang_yi_cn’s picture

Status: Reviewed & tested by the community » Needs work

one problem: If it's a single view used in multilingual site, English doesn't work, only the translated ones work. This kinda can be mitigated by adding both the translated handled and the default / non translated handles.

second problem: as people mentioned before, you need COLLATE utf8_general_ci to do sort in MySQL ignoring accents. However the `locales_target`.`translations` field is BLOB, which sorts by BINARY, so you need to do something like ORDER BY CONVERT( `translation` USING utf8 ) COLLATE utf8_general_ci ASC which I don't believe it's very easy to do in Drupal 7 dynamic query API, even if you can make it, probably breaks SQL compatibility as it's mostly MySQL only solution.

So I think a better solution is just to override the templates and sort using php-intl extension's Collator function and sort the results in PHP.

jasom’s picture

I have "Broken/missing handler" result too.

pmusaraj’s picture

@jasom and @danon1981 I had the same issue after upgrading the module to the current version. I reapplied the patch at #13, cleared caches and the issue was automatically fixed.

devad’s picture

Status: Needs work » Reviewed & tested by the community
FileSize
7.4 KB

Patch #13 re-roll for latest dev

devad’s picture

I have tried to re-roll patch, but it seems that my windows environment broke something and it doesn't apply cleanly at simplytest.me. so, I'm hiding it from files list.

matthieu_collet’s picture

this doens't work with current version, I obtain "Broken / missing handler" and sorting doesn't work

anybody else tried with i18nviews 7.x-3.0-alpha1 ?

devad’s picture

Status: Reviewed & tested by the community » Needs review
FileSize
3.6 KB

Patch #13 re-roll for latest .dev

I have tested patch #32 at simplytest.me. It applies cleanly and works nicely for me.

More reviews are welcome...

So maybe we will have this committed... before new re-roll is needed. :)

Patch #32 can be tested both with alpha1 and current dev.

kienan’s picture

Status: Needs review » Reviewed & tested by the community

looks okay to me. applied and test on a site. I didn't do a full review, but it seems okay at a glance. marking rtbc

nironan’s picture

Worked smoothly for me, thanks!

sthomen’s picture

I tried the latest patch, and it seems to sort terms with a missing translation first (null values first presumably), which is a bit of a problem.