Using Views2 with internationalized database

[marcus.liberman]

Hi

I suspect I know the answer to this one but I will ask...

I am trying to build Drupal Views over a Civi database that has been enabled for multiple languages.

Some fields work just fine, but where a field name has been changed to enable the multiple languages option (e.g.  civicrm.civicrm_contact.first_name has become civicrm_contact.first_name_en_GB) then the select that Views tries to build fails.

Am I the first person to come across this? Has anyone found a workaround?

Thanks & best wishes

Marcus

[Donald Lobo]

i dont think the folks patching it currently use multi-lingual. so you might be the first explorer/adventurer in this area between views2 and civicrm

please do submit your patches back to the community the views2 module gets the most community patches in the civicrm code base

lobo

[Piotr Szotkowski]

I haven’t checked how the Views integration code communicates with the CiviCRM database. Using our DAOs should work transparently, so I assume it does custom queries. We rewrite our queries on the fly in CRM_Core_I18n_Schema::rewriteQuery(), so a well-placed rewriteQuery() call should take care of most/all Views queries.

[Donald Lobo]

hey marcus:

there are two views hook that you can potentially use

hook_views_query_alter
hook_views_pre_execute

You might want to do some research and figure out which hook to use and when to rewrite the query for a multi-lingual db

lobo

[marcus.liberman]

Thanks Lobo & Piotr for your input and help with this. I am sure we will get to the bottom of this. I am really hoping to have sign off on funds this week to get some technical support in for areas such as this

Best wishes

Marcus

[marcus.liberman]

Hi

Before I specify this to allow someone to work on it I thought it would be worth getting input on how Drupal should work with this issue.
The problem arises where the CiviCRM database is set up to be multilingual - presumably because the context in which the solution is operating is a multilingual one. So presumably the views that Drupal presents also need to reflect that multilingual context. So somehow Drupal needs to be able to select an appopriate language to display results in. Mmm...
Does Civi have recorded somewhere the currently selected language for the user? Maybe Drupal should try to use that if it exists? But if the Drupal user looking at the view is not themselves logged in to Civi or is not a Civi user that will not work. Drupal can also be used in a multilingual mode, so maybe it ought to take its language from whatever language Drupal is currently selected to display in. Is this possible?
Civi also has a default language. How could Drupal query to find that default?
Sorry for so many questions but would like to build in a way most useful for others...

Marcus

[hanschri]

I have also run into problems integrating views (2) with internationalized civicrm. Have you made any progress with pointing views to the correct tables (the ones that end with _es_es for spanish, for example)? I have no clue how to do this.

[marcus.liberman]

It is still something we want to tackle, but not on my list right now. When we do some development we will submit it back for inclusion in core code and I will post here. But going to be later this year I'm afraid...

[Piotr Szotkowski]

As for pointing at the locale: CiviCRM defines a global $dbLocale variable which can be appended to a table name, resulting in a view (like $dbLocale = '_pl_PL' resulting in civicrm_contact_pl_PL) which exposes the given locale’s fields under their original names.

To rephrase: if you want to get at civicrm_contact.first_name in a multilang database, you just use civicrm_contact{$dbLocale}.first_name and it should Just Work™ – the $dbLocale is empty on single-language installs.