NodeReference and multilanguage contents / db_rewrite_sql()

As per #1

+1

As a workaround, have you guys tried using a view to provide autocompletion results ? (it's in the "advanced" fieldset under "global settings" on the nodereference field setup page - eg. /admin/content/node-type/xyz/fields/field_xyz)

That said, it'd be good to have a choice on how nodereference fields behave depending on the different language settings, for instance with radio or checkboxes buttons next to the content types checkboxes, with choices such as "provide nodes from any language including language neutral", "only language xyz", or "only language neutral".

Here is a proposed patch (against 6.x-2.8) that allow per field overriding of the "Content selection mode" (aka. i18n_selection_mode). The patch add the a option in the (collapsed) advanced tab when the Internationalization (i18n) module is enabled. The setting is then used when potential references are retrieved (in _nodereference_potential_references()).

Alternative solution: <optgroup> for other languages.

Like this:

<select>
  <option value="101">node 101 in English</option>
  <option value="102">node 102 in English</option>
  <optgroup label="Content in languages other than English">
    <option value="202">node 202 in Hebrew</option>
    <option value="203">node 203 in Arabic</option>
  </optgroup>
</select>

In _nodereference_potential_references_standard():

<?php
  $sql_unfiltered = "SELECT n.nid, n.title AS node_title, n.type AS node_type FROM {node} n $where_clause ORDER BY n.title, n.type";
  $sql_filtered = db_rewrite_sql($sql_unfiltered);
  $references = array();
  foreach (array(FALSE, TRUE) as $filtered) {
    $sql = $filtered ? $sql_filtered : $sql_unfiltered;
    $result = $limit ? db_query_range($sql, $args, 0, $limit) : db_query($sql, $args);
    while ($node = db_fetch_object($result)) {
      $references[$node->nid] = array(
        'title' => $node->node_title,
        'rendered' => check_plain($node->node_title),
        'filtered' => $filtered,
      );
    }
  }
?>

And in nodereference_allowed_values()

<?php
  $options = array();
  $other_language_options = array();
  foreach ($references as $key => $value) {
    if (isset($value['filtered']) && !$value['filtered']) {
      $other_language_options[$key] = $value['rendered'];
    }
    else {
      $options[$key] = $value['rendered'];
    }
  }
  
  if (count($other_language_options)) {
    $unfiltered_caption = t('Content in languages other than @lang', array('@lang' => $GLOBALS['language']->name));
    $options[$unfiltered_caption] = $other_language_options;
  }
?>

Any thoughts?

Myself in
#314721-66: Node reference: results returned for interface language rather than node language:

I'm using cck 6.x-2.8 and i18n 6.x-1.7, and this is not fixed.

My situation:
admin/settings/language/i18n
"Content selection mode": "Current language and language neutral". Why? This was not my decision, but I imagine it was for a good reason.
"Content translation links": I did not check "Switch interface for translating". Why? Because I don't speak Hebrew, and want to be able to visit the node edit form for Hebrew content with an English interface.

Content types "child" and "parent".
"parent" is multilanguage with translations, "child" has no language options.
"child" has a nodereference to "parent".

Visiting hebrew/node/123/edit (node type "child"), I see only Hebrew nodes in nodereference dropdown. I choose one and save. Fine.
Visiting english/node/123/edit, I see only English nodes in the nodereference dropdown. I only came here to change a bit of text, and save. The value for the nodereference is unset. Bad, bad, bad.

As explained in #8, this can damage existing content. Thus, it's a bug.

If you're suggesting sometimes bypassing db_rewrite_sql(), that would be a security hole and cannot be done. I don't use i18n and don't have time to set it up and test, but if there is a problem it cannot be solved by avoiding db_rewrite_sql().

Ok.
So db_rewrite_sql() is important not only for i18n, but also for access checking in general.
I was not aware of that (or didn't think about it).

I see two separate problems:
(1) i18n-specific: Admin might want the interface language to be different from the language of the edited node.
(2) General problem with damaged content. This is very similar to the old problem of access-restricted input formats.

(2):
Assume there is a node/111 with a nodereference pointing to node/222.
Now user/77 wants to edit node/111. This user does have access to node/111, but no access to node/222.
What happens? The nodereference <select> element will be reset to "no value", because node/222 is not part of the options. The user clicks "save", and the value in the nodereference is lost. Damage.

These two problems need two separate solutions.
(1): Temporarily override the i18n content selection mode in some cases. i18n content selection has nothing to do with security, so we can allow a user to see content in different languages. We can use <optgroup> to get some structure into the <select> element.
(2): Either lock the field if user has no access to node/222, or provide an additional option "keep the existing value" / "do not change", that does not reveal the nid or title of node/222.

My Patch in #6 allow to specify, per field, which i18n selection mode to use when building the list of potential references. The i18n selection mode is then used by i18n when it re-writes SQL queries. One of the selection mode is 'off', which means the patch allow Noderefrence to override the i18n content selection mode in some cases (ie. for some fields).

When the i18n mode is set to off for a field, nothing will prevent a node in English to reference a node in French or any other language. This may or may not be a wanted feature. IMHO, control of authorized language for referenced nodes should be implemented as a separated options.

The patch doesn't mess with sql-rewriting, so node access control and other features relying on db_rewrite_sql are not modified.

Also, for better usability, optgroup should probably be added when the list of potential reference contains node in several languages.

Could someone review and comment the patch in #6 ?

@mongolito404 (#12):
I think your patch makes sense, but it does only solve (imo) a part of the problem.

From #11:

(1) I think the interface language is not always the best choice for filtering the nodereference candidates.

• For node editing, the important thing is not the interface language but the language of the node one is currently editing.
• For node creation, or for language neutral nodes, we start with nothing but the interface language. Thus, it can make sense to filter candidates by the interface language. However, what if the user sets the node language to something different from the interface language? Then we have the same problem again.
• Filtering the candidates by language is safe only if the node that is being edited or created has or will have the same language as the interface language (not language neutral), and the user has no possibility to change the node language in the edit form.

(2) Your patch does not protect the field from being reset, if the user has no access to the referenced node. This requires a solution similar to what I mentioned in #11.

I think we rather need a sensible default, that does NOT filter by language, but does sort the candidates in optgroups. Then we can think about an optional setting to hide nodes from other languages.

I agree for (1), filtering on interface language is not always the best choice. Filtering on the language of the created/edited node is generally a better option. But this is harder. For autocomplete and other AJAX driven widget, the current node language can be used as interface language during the background HTTP request. This should only required basic JavaScript for someone who understand the autocomplete widget and AHAH. But for static widget such as select list, more AHAH work is needed as the widget needs to be updated each time the node language is changed.

My patch is meant to be a simple but good enough solution that allow the (language filtering) issue to be worked around while enabling several behaviors. The idea is to quickly get more flexibility on language filtering for user. And to allow refinements for the different i18n mode to be worked on by other developers/contributors. For instance, a possible and easy refinement for field with i18n mode set to off is to validate referenced nodes language against the created/edited node language in an hook_nodeapi implementation.

For (2), IMHO this should be a separated issue. Node access and language filtering are both achieved through db_rewrite_sql() but are actually separated concerns. As pointed by KarenS, bypassing db_rewrite_sql() is not an acceptable solution as this will raise security issues. In addition since db_rewrite_sql() is a pluggable mechanism in Drupal, on a given site you don't know what behaviors are implemented using it and which ones are wanted when retrieving the list of potential references for a nodereference field. So, again IMHO, each problematic behavior (for instance language filtering) should have a separated issue and the solution should be handled at the level of the module providing the behavior (for instance, using i18n mode) and not by bypassing db_rewrite_sql().

The patch in #6 is not properly "rooted" at the CCK basedir. The attached patch should solve this.

Patch #16 working as expected and very usefull.

Thanks @mongolito404

As illustration of my comment about possible refinements for the different i18n mode to be worked on by other developers/contributors, here are
- a hook_nodeapi() implementation to validate that referenced node share the referencee language
- a hook_form_alter() to display the language of potential reference in addition to their title in nodereference_select widget
to be used when the i18nmode of noderefrence fields is set to 'off'.

function MODULE_nodeapi(&$node, $op, $a3 = NULL, $a4 = NULL) {
  switch ($op) {
    case 'validate':
      //Validate NodeReference fields: referenced nodes must share the language of the created/edited node.
      $form =& $a3;
      //Loop over all CCK field defined for the node's content type.
      $fields = content_fields(NULL, $node->type);
      foreach ($fields as &$field) {
        //Process nodereference field with an (array) value
        if ($field['type'] == 'nodereference' && is_array($node->{$field['field_name']})) {
          $args = array();
          //Store the nids of referenced nodes
          foreach($node->{$field['field_name']} as $value) {
            if($value['nid']) {
              $args[] = $value['nid'];
            }
          }
          if ($args) {
            //Disable i18n filtering
            i18n_selection_mode('off');
            //Search for all reference nodes in a different language
            $sql = db_rewrite_sql("SELECT n.nid, n.title, n.language FROM {node} n WHERE nid IN (". db_placeholders($args) .") AND language != '%s'");
            $args[] = $node->language;
            $results = db_query($sql, $args);
            //Collect their titles
            $titles = array();
            while ($ref_node = db_fetch_object($results)) {
              $titles[] = "$ref_node->title [$ref_node->language]";
            }
            //Set error on the form (if any title has been collected)
            if ($titles) {
              form_set_error($field['field_name'], t('Invalid language for @label selection: %titles', array(
                    '@label' => $form[$field['field_name']]['#title'],
                    '%titles' => implode(', ', $titles),
              )));
            }
            //Restore i18n filtering
            i18n_selection_mode('reset');
          }
        }
      }
      break;
  }
}

function MODULE_form_alter(&$form, $form_state, $form_id) {
  //Alter nodereference_select widget to add the nodes language.
  if($form_id === 'page_node_form') {
    $fields = content_fields(null, $form['#node']->type);
    foreach($fields as $field) {
      if($field['widget']['type'] === 'nodereference_select' && is_array($form[$field['field_name']])) {
        //The widget is not build to a full form (select) element, so we
        //register an handler to alter the element after it has been built.
        $form[$field['field_name']]['#after_build'][] = 'MODULE_nodereference_select_after_build';
      }
    }    
  }
}

function MODULE_nodereference_select_after_build(&$nodereference_select_element) {
  //The element is a nodereference_select which contains a optionwidgets_select element
  $optionwidgets_select_element =& $nodereference_select_element[$nodereference_select_element['#columns'][0]];
  //The optionwidgets_select element contains the select element we want to alter
  $select =& $optionwidgets_select_element[$optionwidgets_select_element['#columns'][0]];
  //Now we can fetch the language of all the node in the select element
  $results = db_query('SELECT nid, language FROM {node} WHERE nid in ('. db_placeholders(array_keys($select['#options'])) .')', array_keys($select['#options']));
  //And append it the the existing labels (ie. node titles)
  while($node = db_fetch_object($results)) {
    $select['#options'][$node->nid] = $select['#options'][$node->nid] .' ['. drupal_strtoupper($node->language) .']';
  }
  //Finally we return the alterred nodereference_select element
  return $nodereference_select_element;
}

#16
I tested the attached patch and its working well. The new settings are very useful and the best thing is, that without any changes, the node reference field behaves as usual.

Please integrate this features in the node reference module.

subscribing

I'm not sure if this is in scope for this issue. But I've tested the patch in #16 and it worked to allow me to reference nodes in a different language.
However it these references were filtered out when displaying the node

I've rolled an additional patch to override i18n_selection_mode when sanitizing the field too.
Not sure if this would always be the desire behaviour though?
Patch relies on the previous patch in #16

Also attaching a combined patch of #16 and #21

patch #22 works perfectly for me. on a related note, i also had to apply patch #1 from #1054982: NodeReferrer should set the i18n selection mode to get this to work with nodereferrer.

I rerolled the patch from #22 against the latest 6.x-2.x, fixed a broken link, added a few comments and cleaned up the code.

patch #22 works fine for me, did give me 4 whitespace errors.
nodereference_671406_combined.patch:53: trailing whitespace.
nodereference_671406_combined.patch:59: trailing whitespace.
nodereference_671406_combined.patch:67: trailing whitespace.
nodereference_671406_combined.patch:72: trailing whitespace.

it would be neat if you could enable a "fixed" language for each field.
e.g. on my site I have:

reference NL:
reference FR:
reference EN:

it would be cool if each field only shows nodes for the language it's configured for.

These whitespace problems have already been addressed in the patch in #24.

Patch #24 still needs to be applied whenever CCK is updated (like a security release). Any idea if there's anything blocking this patch to be accepted? :)