Clickable sort does not work with relationships

Hi,

I have a view which uses a table style. All columns are sortable. It has a relationship (node reference). The sorting of the table does not work when I sort by a (cck)field of the referenced content type.

I could discover, that the generated SQL ist not using the relationship:

Current SQL:

SELECT node_node_data_field_main_ref_node_data_field_main_nr.field_main_nr_value AS node_node_data_field_main_ref_node_data_field_main_nr_field_,
node_node_data_field_main_ref.type AS node_node_data_field_main_ref_type,
node_node_data_field_main_ref.nid AS node_node_data_field_main_ref_nid,
node_node_data_field_main_ref.vid AS node_node_data_field_main_ref_vid,
node_node_data_field_main_ref.title AS node_node_data_field_main_ref_title,
node.title AS node_title,
node.nid AS nid,
node_data_field_main_nr.field_main_nr_value AS node_node_data_field_main_ref_node_data_field_main_nr_field__1
 FROM node node 
 LEFT JOIN content_type_test_dependent node_data_field_main_ref ON node.vid = node_data_field_main_ref.vid
 LEFT JOIN node node_node_data_field_main_ref ON node_data_field_main_ref.field_main_ref_nid = node_node_data_field_main_ref.nid
 LEFT JOIN content_type_test_main node_node_data_field_main_ref_node_data_field_main_nr ON node_node_data_field_main_ref.vid = node_node_data_field_main_ref_node_data_field_main_nr.vid
 LEFT JOIN content_type_test_main node_data_field_main_nr ON node.vid = node_data_field_main_nr.vid
 WHERE node.type in ('test_dependent')
   ORDER BY node_node_data_field_main_ref_node_data_field_main_nr_field__1 ASC

SQL how it should be:

SELECT node_node_data_field_main_ref_node_data_field_main_nr.field_main_nr_value AS node_node_data_field_main_ref_node_data_field_main_nr_field_,
node_node_data_field_main_ref.type AS node_node_data_field_main_ref_type,
node_node_data_field_main_ref.nid AS node_node_data_field_main_ref_nid,
node_node_data_field_main_ref.vid AS node_node_data_field_main_ref_vid,
node_node_data_field_main_ref.title AS node_node_data_field_main_ref_title,
node.title AS node_title,
node.nid AS nid,
node_data_field_main_nr.field_main_nr_value AS node_node_data_field_main_ref_node_data_field_main_nr_field__1
 FROM node node 
 LEFT JOIN content_type_test_dependent node_data_field_main_ref ON node.vid = node_data_field_main_ref.vid
 LEFT JOIN node node_node_data_field_main_ref ON node_data_field_main_ref.field_main_ref_nid = node_node_data_field_main_ref.nid
 LEFT JOIN content_type_test_main node_node_data_field_main_ref_node_data_field_main_nr ON node_node_data_field_main_ref.vid = node_node_data_field_main_ref_node_data_field_main_nr.vid
 LEFT JOIN content_type_test_main node_data_field_main_nr ON node_node_data_field_main_ref.vid = node_data_field_main_nr.vid
 WHERE node.type in ('test_dependent')
   ORDER BY node_node_data_field_main_ref_node_data_field_main_nr_field__1 ASC

The bug is in the fourth LEFT JOIN.

The expresion:
LEFT JOIN content_type_test_main node_data_field_main_nr ON node.vid = node_data_field_main_nr.vid
should be:
LEFT JOIN content_type_test_main node_data_field_main_nr ON node_node_data_field_main_ref.vid = node_data_field_main_nr.vid

Sorry, do not know where it is in the code. I tried to debug it, but views is very complecated. Hope someone can help.

Thanks.

PS: Mabe the export code of my view could help

$view = new view;
$view->name = 'test_sort';
$view->description = '';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 3.0-alpha1;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Defaults */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->display->display_options['access']['type'] = 'none';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['style_plugin'] = 'table';
$handler->display->display_options['style_options']['columns'] = array(
  'field_main_nr_value' => 'field_main_nr_value',
  'title' => 'title',
  'title_1' => 'title_1',
);
$handler->display->display_options['style_options']['default'] = 'field_main_nr_value';
$handler->display->display_options['style_options']['info'] = array(
  'field_main_nr_value' => array(
    'sortable' => 1,
    'align' => '',
    'separator' => '',
  ),
  'title' => array(
    'sortable' => 1,
    'align' => '',
    'separator' => '',
  ),
  'title_1' => array(
    'sortable' => 1,
    'align' => '',
    'separator' => '',
  ),
);
$handler->display->display_options['style_options']['override'] = 1;
$handler->display->display_options['style_options']['sticky'] = 0;
/* Relationship: Content: Main Reference (field_main_ref) */
$handler->display->display_options['relationships']['field_main_ref_nid']['id'] = 'field_main_ref_nid';
$handler->display->display_options['relationships']['field_main_ref_nid']['table'] = 'node_data_field_main_ref';
$handler->display->display_options['relationships']['field_main_ref_nid']['field'] = 'field_main_ref_nid';
$handler->display->display_options['relationships']['field_main_ref_nid']['required'] = 0;
/* Field: Content: Nr (field_main_nr) */
$handler->display->display_options['fields']['field_main_nr_value']['id'] = 'field_main_nr_value';
$handler->display->display_options['fields']['field_main_nr_value']['table'] = 'node_data_field_main_nr';
$handler->display->display_options['fields']['field_main_nr_value']['field'] = 'field_main_nr_value';
$handler->display->display_options['fields']['field_main_nr_value']['relationship'] = 'field_main_ref_nid';
$handler->display->display_options['fields']['field_main_nr_value']['label'] = 'Nr';
$handler->display->display_options['fields']['field_main_nr_value']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['field_main_nr_value']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['field_main_nr_value']['alter']['trim'] = 0;
$handler->display->display_options['fields']['field_main_nr_value']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['field_main_nr_value']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['field_main_nr_value']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['field_main_nr_value']['alter']['html'] = 0;
$handler->display->display_options['fields']['field_main_nr_value']['hide_empty'] = 0;
$handler->display->display_options['fields']['field_main_nr_value']['empty_zero'] = 0;
$handler->display->display_options['fields']['field_main_nr_value']['link_to_node'] = 0;
/* Field: Node: Title */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
$handler->display->display_options['fields']['title']['relationship'] = 'field_main_ref_nid';
$handler->display->display_options['fields']['title']['label'] = 'Main-Title';
$handler->display->display_options['fields']['title']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['title']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['title']['alter']['trim'] = 0;
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['title']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['title']['alter']['html'] = 0;
$handler->display->display_options['fields']['title']['hide_empty'] = 0;
$handler->display->display_options['fields']['title']['empty_zero'] = 0;
$handler->display->display_options['fields']['title']['link_to_node'] = 1;
/* Field: Node: Title */
$handler->display->display_options['fields']['title_1']['id'] = 'title_1';
$handler->display->display_options['fields']['title_1']['table'] = 'node';
$handler->display->display_options['fields']['title_1']['field'] = 'title';
$handler->display->display_options['fields']['title_1']['label'] = 'Dependent-Title';
$handler->display->display_options['fields']['title_1']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['title_1']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['title_1']['alter']['trim'] = 0;
$handler->display->display_options['fields']['title_1']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['title_1']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['title_1']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['title_1']['alter']['html'] = 0;
$handler->display->display_options['fields']['title_1']['hide_empty'] = 0;
$handler->display->display_options['fields']['title_1']['empty_zero'] = 0;
$handler->display->display_options['fields']['title_1']['link_to_node'] = 1;
/* Filter: Node: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
  'test_dependent' => 'test_dependent',
);

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->display->display_options['path'] = 'test-sort';