'Other' option for Relationships filters in views creates a non valid query [#527970]

Using the 'Other' option for Relationships filters in views causes an SQl Error

I have nonstandard tags and functions for agreggate votes, so i used the 'other' options but it looks like views takes value_type_other, tag_other and function_other in its SQL generation.

Here is an extract of the view, just the interesting bits with the votingapi array.

$handler = $view->new_display('block', 'Bloc dernieres offres marchands', 'block_2');
$handler->override_option('relationships', array(
  'votingapi_cache' => array(
    'label' => 'Vote results',
    'required' => 0,
    'votingapi' => array(
      'value_type' => '',
      'value_type_other' => '',
      'tag' => 'thumb',
      'tag_other' => 'thumb',
      'function' => 'positive',
      'function_other' => 'positive',
    ),
    'id' => 'votingapi_cache',
    'table' => 'node',
    'field' => 'votingapi_cache',
    'override' => array(
      'button' => 'Utiliser la valeur par défaut',
    ),
    'relationship' => 'none',
  ),
));

and here is the sql part with the resulting error:

(votingapi_cache_node_thumb_thumb_positive_positive.content_type = 'node' AND votingapi_cache_node_thumb_thumb_positive_positive.tag = 'thumb' AND votingapi_cache_node_thumb_thumb_positive_positive.tag_other = 'thumb' AND votingapi_cache_node_thumb_thumb_positive_positive.function = 'positive' AND votingapi_cache_node_thumb_thumb_positive_positive.function_other = 'positive')

The tag_other and function_other fields do not exist in the votingapi_cache table.

Thanks for your great module !!

Comments

Comment #1

te-brian commented 24 July 2009 at 21:35

I'm getting this error as well. Let me know if more details would help.

Comment #2

te-brian commented 24 July 2009 at 22:01

As a quick fix I isolated the point where these '*_other' values are being saved into the view. Its in the "options_form" in votingapi_views_handler_relationship.inc. I modified the submit function to only allow through 'value' and 'tag' and not 'value_other' and 'tag_other'.

lines 137 - 152

  /**
   * Perform any necessary changes to the form values prior to storage.
   * There is no need for this function to actually store the data.
   */
  function options_submit($form, &$form_state) {
    $v = $form_state['values']['options']['votingapi'];
    if ($v['value_type'] == '**OTHER**') {
      $form_state['values']['options']['votingapi']['value_type'] = $v['value_type_other'];
      unset($form_state['values']['options']['votingapi']['value_type_other']);
    }

    if ($v['tag'] == '**OTHER**') {
      $form_state['values']['options']['votingapi']['tag'] = $v['tag_other'];
      unset($form_state['values']['options']['votingapi']['tag_other']);
    }

    if ($v['function'] == '**OTHER**') {
      $form_state['values']['options']['votingapi']['function'] = $v['function_other'];
      unset($form_state['values']['options']['votingapi']['function_other']);
    }
  }

I haven't done much testing but this could help if u need a quick fix.

Comment #3

te-brian commented 24 July 2009 at 22:43

Ok, self edit. I ran into a broken query with the above code. This is working... for now.

  /**
   * Perform any necessary changes to the form values prior to storage.
   * There is no need for this function to actually store the data.
   */
  function options_submit($form, &$form_state) {
    $v = $form_state['values']['options']['votingapi'];
    if ($v['value_type'] == '**OTHER**') {
      $form_state['values']['options']['votingapi']['value_type'] = $v['value_type_other'];
    }

    if ($v['tag'] == '**OTHER**') {
      $form_state['values']['options']['votingapi']['tag'] = $v['tag_other'];
    }

    if ($v['function'] == '**OTHER**') {
      $form_state['values']['options']['votingapi']['function'] = $v['function_other'];
    }
    
    unset($form_state['values']['options']['votingapi']['function_other']);
    unset($form_state['values']['options']['votingapi']['tag_other']);
    unset($form_state['values']['options']['votingapi']['value_type_other']);
  }

Comment #4

brad.bulger commented 13 August 2009 at 00:40

i had a similar problem, but i was not using 'Other' anywhere in the relationship. still got an unknown column error, for 'votingapi_cache_node_points_points_vote_vote_sum_sum.value_type_' (yes, ending in an underscore, not a typo)

@te-brian's unsets fixed it for now, which is great. i suspect the real problem is elsewhere but i am too new to Views 2 to find it, it's all still a bit overwhelming.