Relationships, dynamic valuelists in multilingual mode

Archived from the Xataface Users forum.

the_woz — Thu Sep 29, 2011 4:46 pm

I’m trying to implement multilingual support in the xataface application I’m working on, but I have the following problem:

With multilingual_content=0 everything works ok, relationships, dynamic valuelists (for yui_autocomplete) including field translations. Translation action shows for tables with multiple languages but selecting source or destination language have no effect (reverts to default language)

With multilingual_content=1 everything works ok only on isolated tables (no relationships or dynamic valuelists). The other tables work ok only for the default language, selecting other language results in a “500 internal server error”
Commenting out the ‘vocabulary’ definition in each table fields.ini gets the list action to work for all languages, but the details/edit and translate actions returns a blank page.
The internal server error still shows for my master table when selecting other than the default language.

My database is built this way:

Code: Select all

`Magazine
       |
       -> Issue
               |
               |-> Article
               |-> local_link
                -> ext_link

Scan_project

Users`

My config.ini:

Code: Select all

default_oe = UTF-8 default_ie = UTF-8 default_language = en multilingual_content = 1 [_database] ... ... [languages] en = en es = es

What I’m missing?
And how do I define different dynamic valuelists for each language?

---

jonbfl — Thu Sep 29, 2011 6:40 pm

“I should know this” I don’t quite have the language, maybe I can find an example

lets take the Issues -> Articles

this is like my Cases -> Victims

Some of my Cases have one victim, others have more than one - and I wanted to be able to add them on the fly.

here is how they are tied together in my application -

in the tables/cases/ folder I have this relationship.ini

Code: Select all

[victims] __sql__ = "SELECT * FROM CasesVictims, victims WHERE victims.id_victims = CasesVictims.id_victims AND CasesVictims.id_cases = '$id_cases'"

that points to a Join Table CasesVictims that has this structure

CREATE TABLE IF NOT EXISTS CasesVictims (
id\_cases int(10) NOT NULL,
id\_victims int(10) NOT NULL,
PRIMARY KEY (id\_cases,id\_victims)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’this is the many-to-many join table for cases_to_victims’;

and in the tables/victims folder I have another relationships.ini. It has a reciprocal entry for cases (points back to it) The other two entries are for attributes for Victims (like say Author and Subject Type might be for ‘Articles’)

Code: Select all

[cases] __sql__ = "SELECT * FROM CasesVictims, cases WHERE cases.id_cases = CasesVictims.id_cases AND CasesVictims.id_victims = '$id_victims'" [hr_violation_mo] __sql__ = "SELECT * FROM VictimsHr_violation_mo, hr_violation_mo WHERE hr_violation_mo.idhr_violation_mo = VictimsHr_violation_mo.idhr_violation_mo AND VictimsHr_violation_mo.id_victims = '$id_victims' " [hr_violation_code] __sql__ = "SELECT * FROM VictimsHr_violation_code, hr_violation_code WHERE hr_violation_code.idhr_violation_code = VictimsHr_violation_code.idhr_violation_code AND VictimsHr_violation_code.id_victims = '$id_victims' "

Now I have Language variants of each of the files -

cases
cases_en
cases_es

victims
victims_en
victims_es

Here’s the cases set

Code: Select all

`` CREATE TABLE IF NOT EXISTS cases (
  id_cases int(10) NOT NULL AUTO_INCREMENT,
  case_date date NOT NULL,
  case_title varchar(45) NOT NULL,
  case_comment text,
  case_narrative text COMMENT ‘this will be a translated field’,
  case_code varchar(20) DEFAULT ‘Future Field’ COMMENT ‘this is our field to “stuff” - date$ + ‘’-‘’ + record string padded to 5 positions\n’,
  PRIMARY KEY (id_cases)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT=’This holds the Master record and is the source table’ AUTO_INCREMENT=14 ;

CREATE TABLE IF NOT EXISTS cases_en (
  id_cases int(10) NOT NULL,
  case_title varchar(45) DEFAULT NULL,
  case_narrative text COMMENT ‘this will be a translated field’,
  PRIMARY KEY (id_cases)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’English Translated fields table\n’;

CREATE TABLE IF NOT EXISTS cases_es (
  id_cases int(10) NOT NULL,
  case_title varchar(45) DEFAULT NULL,
  case_narrative text COMMENT ‘this will be a translated field’,
  PRIMARY KEY (id_cases)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’Castillian Translated fields table\n’; ``

and so on …

then Xataface does its thing - and I get a new tab in the Cases views that is labeled ‘victims’ -
when I go to that tab i get two choices - Add a new Victim record or Use an existing Victim record -

This is what I see when I click that ‘Victims’ tab -

The Join Table tells Xataface the Index value in the set of records to fetch the correct translated text. (or to create NEW translated text)

I hope this does not yield more confusion and I’m sure there are other ways to do this as well, but this method has worked for me. I’m sure Steve will have a much more lucid explanation and, probably, an easier to follow (and construct) method

Good Luck

---

the_woz — Fri Sep 30, 2011 12:33 pm

I think I got it, thanks jonbl!

So basically to get relationships working with multlingual_content=1 I need convert all one-to-many relationships to many-to-many relationships by adding a join table.
I’ll try this, in fact many-to-many relationships will improve my application in some cases

I still have the doubt about yui_autocomplete vocabularies with multilingual content on. I can convert two of the three dynamic valuelist I use to static valuelists. But the third one I need to be dynamic.
This valuelist lets the user select which section an article belongs to (ie. News, Reviews, etc) or enter a new one if none matches.

But, one step at a time.

---

shannah — Fri Sep 30, 2011 7:11 pm

So basically to get relationships working with multlingual_content=1 I need convert all one-to-many relationships to many-to-many relationships by adding a join table.

No. It should work with any existing relationship. The key for you is to check your error log. Server error 500 simply means an error occurred. It doesn’t say what the error is. It is possible that the parser is having trouble with a particular query. In which case it would be nice to know what the query is. It will say in the error log.

-Steve

---

the_woz — Sat Oct 01, 2011 11:09 am

shannah wrote:

So basically to get relationships working with multlingual_content=1 I need convert all one-to-many relationships to many-to-many relationships by adding a join table.

No. It should work with any existing relationship. The key for you is to check your error log. Server error 500 simply means an error occurred. It doesn’t say what the error is. It is possible that the parser is having trouble with a particular query. In which case it would be nice to know what the query is. It will say in the error log.

-Steve

Hi Steve, yeah, after I tought about it more camly about it requiring many-to-many relationships didn’t make much sense.

I have no access to the server logs, but it seems the error 500 was caused by something not properly initialized in one of the dataface private tables.
The error went away when I ‘translated’ one record from the default language to another.

But before that, I got rid of the blank pages problem by removing a couple of self referencing relationships from my main table.

My application will be used to catalog and index vintage computer magazines, several of this magazines changed names through it’s histories and merged one another.
To allow for such cases I have a ‘follows_id’ field in the Magazine table (called mag_list in reality).
Then I define a couple of relationships as follows:

Code: Select all

`[Followed_by]
mag_list.follows_id = “$m_id”
meta:class = children
action:visible = 0
section:visible = 1

[Preceded_by]
mag_list.m_id = “$follows_id”
meta:class = parent`

m_id is the magazine id field

With these two commented out the application works perfectly, but if I enable one or both, I get an error of this class:

Code: Select all

Fatal error: Failed parsing SQL query on select: select * fro where '$m_id'='$follows_id' . The Error was Parse error: Unexpected token "where" on line 1 select * fro where '$m_id'='$follows_id' ^ found: "where" in /home3/retromag/public_html/xf/lib/SQL/Parser.php on line 1752

(this is for the ‘Preceded_by’ relationship only)

---

the_woz — Sun Oct 02, 2011 9:22 pm

It seems all the problem was due to a server glitch.

I fixed the relationships error I reported in my previous post by reverting the definitions to the original __sql__ queries I was using before all these problems with the error 500.

Now with those problems behind…

How can I define a dynamic valuelist for each language?

For the ‘article’ table, the section valuelist is defined this way:

Code: Select all

[Section] __sql__ = "SELECT section FROM article ORDER BY section ASC"

This valuelist gets the section values from the default table, how can be made to get the values from the selected language? (article_en or article_es)

---

the_woz — Mon Oct 03, 2011 12:53 pm

Ok, I got it working, found Steve’s tip in this thread: http://xataface.com/forum/viewtopic.php?p=22289#p2228
And wrote a custom valuelist handler in the delegate class for the ‘article’ table, like this:

Code: Select all

`<?
class tables_article {

   function valuelist__sections(){
   
      $app =& Dataface_Application::getInstance();
      $query =& $app->getQuery();
      $slang = ($app->conf[‘lang’]==’en’)? ‘’ : ‘’.$app->_conf[‘lang’]; //Selected language

      $sql = “select section from article”.$slang.” order by section”;
      $res = mysql_query($sql);
      $out = array();

      while ($row = mysql_fetch_assoc($res)) $out[$row[‘section’]] = $row[‘section’];
      return $out;
   }

}
?>`

Now all works like intended

---

jonbfl — Mon Oct 03, 2011 1:41 pm

LOL - since I was ‘about’ to work on something just like that for my Regions/Provinces tables, can I ask a question?

Is there an underlying relationship.ini there????

or

NOT (as I think is the case)

Thanks!

---

the_woz — Tue Oct 04, 2011 7:46 pm

Hi jonbfl, you’re right, there’s no relationship using the section column.

I don’t know how have you defined your Regions/Provinces tables, but if ie. you have a set with the provinces names you can use something like this in the table’s delegated class:

(This is for the schedule in my magazine table, defined as a select widget with ‘Scheledules’ as vocabulary)

Code: Select all

`function valuelist__Schedules(){
   
      $app =& Dataface_Application::getInstance();

      switch ($app->_conf[‘lang’]) {
         case “es”: $out = array(‘weekly’ => ‘Semanal’,’bi-weekly’ => ‘Quincenal’,’monthly’ => ‘Mensual’,’bi-monthly’ => ‘Bimestral’,’quaterly’ => ‘Cuatrimestral’,’semiannually’ => ‘Semestral’,’yearly’ => ‘Anual’,’other’ => ‘Otra’);
            break;
         case “en”:
         default:   $out = array(‘weekly’ => ‘Weekly’,’bi-weekly’ => ‘Bi-Weekly’,’monthly’ => ‘Monthly’,’bi-monthly’ => ‘Bi-Monthly’,’quaterly’ => ‘Quarterly’,’semiannually’ => ‘Semiannually’,’yearly’ => ‘Yearly’,’other’ => ‘Other’);
            break;
      }

      return $out;
   }`

This way the schedule type is displayed in the selected language, but the correct set value is always stored in the default table.

---

jonbfl — Wed Oct 05, 2011 5:45 am

OK, thanks -

I have my ‘for now’ beta ready for testing just now, and I have used a ‘standard’ Xataface Select widget with a ‘master language only’ SQL driven vocabulary statement that gets the ISO province/region/department codes. (ditto country codes) Works OK (for now)

I’m going to need the other part for our publication phase, so sometime next week probably, I’ll be seeing If can can wrangle your logic into my requirement, thanks a lot.