Relationship based on another relationship [#489734]

I've create a relationship (content profile) which uses another relationship. This code was working in views 2.3, but now that I upgrade to 2.5 or 2.6 it produces this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= node_users.uid AND node_users.type = 'profile' WHERE node_comments.type in ('' at line 8 query: SELECT COUNT(*) FROM (SELECT COUNT(comments.cid) AS cid, users_comments.name AS users_comments_name, users_comments.picture AS users_comments_picture, node_users.title AS node_users_title FROM comments comments INNER JOIN node node_comments ON comments.nid = node_comments.nid INNER JOIN users users_comments ON comments.uid = users_comments.uid INNER JOIN node node_users ON .uid = node_users.uid AND node_users.type = 'profile' WHERE node_comments.type in ('biblio') GROUP BY users_comments_name, users_comments_picture, node_users_title, users_comments_name, users_comments_picture, node_users_title ORDER BY cid DESC ) count_alias in .../sites/all/modules/views/includes/view.inc on line 729.

As you may found out the error is because ON .uid which does not have any table name before. The following is my views configuration:

$view = new view;
$view->name = 'biblio_comments';
$view->description = 'Biblio Comments';
$view->tag = 'biblio comments';
$view->view_php = '';
$view->base_table = 'comments';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('relationships', array(
  'nid' => array(
    'label' => 'Node',
    'required' => 1,
    'id' => 'nid',
    'table' => 'comments',
    'field' => 'nid',
    'relationship' => 'none',
  ),
  'uid' => array(
    'label' => 'User',
    'required' => 1,
    'id' => 'uid',
    'table' => 'comments',
    'field' => 'uid',
    'relationship' => 'none',
  ),
  'content_profile_rel' => array(
    'label' => 'Content Profile',
    'required' => 1,
    'type' => 'profile',
    'id' => 'content_profile_rel',
    'table' => 'users',
    'field' => 'content_profile_rel',
    'relationship' => 'uid',
  ),
));
$handler->override_option('fields', array(
  'name' => array(
    'label' => 'Name',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
    ),
    'link_to_user' => 0,
    'overwrite_anonymous' => 0,
    'anonymous_text' => '',
    'exclude' => 1,
    'id' => 'name',
    'table' => 'users',
    'field' => 'name',
    'relationship' => 'uid',
  ),
  'picture' => array(
    'label' => '',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 1,
      'path' => 'users/[name]',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
    ),
    'exclude' => 0,
    'id' => 'picture',
    'table' => 'users',
    'field' => 'picture',
    'relationship' => 'uid',
  ),
  'title' => array(
    'label' => '',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 1,
      'path' => 'paperreviews/[name]',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
    ),
    'link_to_node' => 0,
    'exclude' => 0,
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'relationship' => 'content_profile_rel',
  ),
  'cid' => array(
    'label' => 'Comments Count',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
    ),
    'link_to_comment' => 0,
    'exclude' => 0,
    'id' => 'cid',
    'table' => 'comments',
    'field' => 'cid',
    'relationship' => 'none',
  ),
  'views_sql_groupedfields' => array(
    'label' => 'Group By Fields',
    'alter' => array(
      'alter_text' => FALSE,
      'text' => '',
      'make_link' => FALSE,
      'path' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'trim' => FALSE,
      'max_length' => '',
      'word_boundary' => TRUE,
      'ellipsis' => TRUE,
      'html' => FALSE,
    ),
    'exclude' => '1',
    'id' => 'views_sql_groupedfields',
    'table' => 'views_groupby',
    'field' => 'views_sql_groupedfields',
    'relationship' => 'none',
    'views_groupby_fields_to_group' => array(
      'name' => 'name',
      'picture' => 'picture',
      'title' => 'title',
    ),
    'views_groupby_sql_function' => 'count',
    'views_groupby_fields_to_aggregate' => array(
      'cid' => 'cid',
    ),
    'views_groupby_field_sortby' => 'cid',
    'views_groupby_sortby_direction' => 'desc',
    'override' => array(
      'button' => 'Override',
    ),
  ),
));
$handler->override_option('filters', array(
  'type' => array(
    'operator' => 'in',
    'value' => array(
      'biblio' => 'biblio',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'type',
    'table' => 'node',
    'field' => 'type',
    'relationship' => 'nid',
  ),
));
$handler->override_option('access', array(
  'type' => 'role',
  'role' => array(
    '8' => 8,
  ),
));
$handler->override_option('cache', array(
  'type' => 'none',
));
$handler->override_option('title', 'Paper Review Statistics');
$handler->override_option('use_ajax', TRUE);
$handler->override_option('items_per_page', 9);
$handler->override_option('use_pager', 'mini');
$handler->override_option('style_plugin', 'grid');
$handler->override_option('style_options', array(
  'grouping' => '',
  'columns' => '3',
  'alignment' => 'horizontal',
));
$handler = $view->new_display('page', 'Textual', 'page_1');
$handler->override_option('fields', array(
  'name' => array(
    'label' => 'Name',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
    ),
    'link_to_user' => 0,
    'overwrite_anonymous' => 0,
    'anonymous_text' => '',
    'exclude' => 1,
    'id' => 'name',
    'table' => 'users',
    'field' => 'name',
    'relationship' => 'uid',
  ),
  'picture' => array(
    'label' => '',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 1,
      'path' => 'users/[name]',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
    ),
    'exclude' => 0,
    'id' => 'picture',
    'table' => 'users',
    'field' => 'picture',
    'relationship' => 'uid',
  ),
  'title' => array(
    'label' => '',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 1,
      'path' => 'paperreviews/[name]',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
    ),
    'link_to_node' => 0,
    'exclude' => 0,
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'relationship' => 'content_profile_rel',
  ),
  'cid' => array(
    'label' => 'Comments Count',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
    ),
    'link_to_comment' => 0,
    'exclude' => 0,
    'id' => 'cid',
    'table' => 'comments',
    'field' => 'cid',
    'relationship' => 'none',
  ),
  'views_sql_groupedfields' => array(
    'label' => 'Group By Fields',
    'alter' => array(
      'alter_text' => FALSE,
      'text' => '',
      'make_link' => FALSE,
      'path' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'trim' => FALSE,
      'max_length' => '',
      'word_boundary' => TRUE,
      'ellipsis' => TRUE,
      'html' => FALSE,
    ),
    'exclude' => '1',
    'id' => 'views_sql_groupedfields',
    'table' => 'views_groupby',
    'field' => 'views_sql_groupedfields',
    'relationship' => 'none',
    'views_groupby_fields_to_group' => array(
      'name' => 'name',
      'picture' => 'picture',
      'title' => 'title',
    ),
    'views_groupby_sql_function' => 'count',
    'views_groupby_fields_to_aggregate' => array(
      'cid' => 'cid',
    ),
    'views_groupby_field_sortby' => 'title',
    'views_groupby_sortby_direction' => 'asc',
    'override' => array(
      'button' => 'Use default',
    ),
  ),
));
$handler->override_option('path', 'biblio_comments');
$handler->override_option('menu', array(
  'type' => 'normal',
  'title' => 'Paper Review',
  'description' => '',
  'weight' => '0',
  'name' => 'navigation',
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
));
$handler = $view->new_display('page', 'Chart', 'page_2');
$handler->override_option('fields', array(
  'name' => array(
    'label' => 'Name',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
    ),
    'link_to_user' => 0,
    'overwrite_anonymous' => 0,
    'anonymous_text' => '',
    'exclude' => 1,
    'id' => 'name',
    'table' => 'users',
    'field' => 'name',
    'relationship' => 'uid',
  ),
  'picture' => array(
    'label' => '',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 1,
      'path' => 'users/[name]',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
    ),
    'exclude' => 0,
    'id' => 'picture',
    'table' => 'users',
    'field' => 'picture',
    'relationship' => 'uid',
  ),
  'title' => array(
    'label' => '',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 1,
      'path' => 'paperreviews/[name]',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
    ),
    'link_to_node' => 0,
    'exclude' => 0,
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'relationship' => 'content_profile_rel',
  ),
  'cid' => array(
    'label' => 'Comments Count',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
    ),
    'link_to_comment' => 0,
    'exclude' => 0,
    'id' => 'cid',
    'table' => 'comments',
    'field' => 'cid',
    'relationship' => 'none',
  ),
  'views_sql_groupedfields' => array(
    'label' => 'Group By Fields',
    'alter' => array(
      'alter_text' => FALSE,
      'text' => '',
      'make_link' => FALSE,
      'path' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'trim' => FALSE,
      'max_length' => '',
      'word_boundary' => TRUE,
      'ellipsis' => TRUE,
      'html' => FALSE,
    ),
    'exclude' => '1',
    'id' => 'views_sql_groupedfields',
    'table' => 'views_groupby',
    'field' => 'views_sql_groupedfields',
    'relationship' => 'none',
    'views_groupby_fields_to_group' => array(
      'name' => 'name',
      'picture' => 'picture',
      'title' => 'title',
    ),
    'views_groupby_sql_function' => 'count',
    'views_groupby_fields_to_aggregate' => array(
      'cid' => 'cid',
    ),
    'views_groupby_field_sortby' => 'title',
    'views_groupby_sortby_direction' => 'asc',
    'override' => array(
      'button' => 'Use default',
    ),
  ),
));
$handler->override_option('title', 'Top 8 Reviewers');
$handler->override_option('items_per_page', 8);
$handler->override_option('use_pager', '0');
$handler->override_option('style_plugin', 'charts');
$handler->override_option('style_options', array(
  'views_charts_series_fields' => array(
    'cid' => 'cid',
  ),
  'views_charts_x_labels' => 'name',
  'width' => '600',
  'height' => '300',
  'engine' => 'open-flash',
  'type' => 'bar',
  'y_min' => '0',
  'y_legend' => '',
));
$handler->override_option('path', 'biblio_comments/chart');
$handler->override_option('menu', array(
  'type' => 'tab',
  'title' => 'Chart',
  'description' => '',
  'weight' => '1',
  'name' => 'navigation',
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
));
$handler = $view->new_display('page', 'Default Tab', 'page_3');
$handler->override_option('path', 'biblio_comments/default');
$handler->override_option('menu', array(
  'type' => 'default tab',
  'title' => 'Reviews',
  'description' => '',
  'weight' => '0',
  'name' => 'navigation',
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => '0',
));

Comments

Comment #1

masood_mj commented 23 June 2009 at 17:36

bump!

Comment #2

marcingy commented 25 June 2009 at 21:14

I have having a similar sort of issue the following view work fine under 6.2.3

$view = new view;
$view->name = 'latest_featured_fitpros';
$view->description = '';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('relationships', array(
  'field_featured_user_uid' => array(
    'label' => 'User',
    'required' => 1,
    'delta' => -1,
    'id' => 'field_featured_user_uid',
    'table' => 'node_data_field_featured_user',
    'field' => 'field_featured_user_uid',
    'relationship' => 'none',
  ),
  'content_profile_rel' => array(
    'label' => 'Content Profile',
    'required' => 1,
    'type' => 'profile',
    'id' => 'content_profile_rel',
    'table' => 'users',
    'field' => 'content_profile_rel',
    'relationship' => 'field_featured_user_uid',
  ),
));
$handler->override_option('fields', array(
  'nid' => array(
    'label' => 'Nid',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
    ),
    'link_to_node' => 0,
    'exclude' => 1,
    'id' => 'nid',
    'table' => 'node',
    'field' => 'nid',
    'relationship' => 'none',
  ),
  'field_profile_image_fid' => array(
    'label' => '',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 1,
      'path' => 'node/[nid]',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
    ),
    'link_to_node' => 0,
    'label_type' => 'none',
    'format' => 'profile_photo_55x55_default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_profile_image_fid',
    'table' => 'node_data_field_profile_image',
    'field' => 'field_profile_image_fid',
    'relationship' => 'content_profile_rel',
  ),
));
$handler->override_option('sorts', array(
  'created' => array(
    'order' => 'DESC',
    'granularity' => 'second',
    'id' => 'created',
    'table' => 'node',
    'field' => 'created',
    'relationship' => 'none',
  ),
));
$handler->override_option('filters', array(
  'status' => array(
    'operator' => '=',
    'value' => '1',
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'status',
    'table' => 'node',
    'field' => 'status',
    'relationship' => 'none',
  ),
  'type' => array(
    'operator' => 'in',
    'value' => array(
      'feature_trainer' => 'feature_trainer',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'type',
    'table' => 'node',
    'field' => 'type',
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('items_per_page', 4);
$handler->override_option('style_plugin', 'grid');
$handler->override_option('style_options', array(
  'grouping' => '',
  'columns' => '2',
  'alignment' => 'horizontal',
));

However when views is updated to 6.2.4 or higher the nid are pulled back but the images are not found for each of the users. After checking the logs I get a simalar sql error related to .uid.

Comment #3

liquidcms commented 29 June 2009 at 03:14

I have similar issue. I have a Users view with following relationships:

- user relates to content_profile node (p1) for that user
- p1 has a user ref field which is used to relate to a 2nd user
- that user relates to a 2nd content_profile (p2) node

I can no longer see the content from p2.

This works in view 2.3 but is busted in 2.6 or latest dev.

Comment #4

merlinofchaos commented 1 July 2009 at 15:43

Status:

Active

» Postponed (maintainer needs more info)

The common factor in both of these issues is the content_profile module, which may need to be updated. Perhaps you should check the content profile queue.

Comment #5

masood_mj commented 5 July 2009 at 11:56

Status:

Postponed (maintainer needs more info)

» Fixed

I have updated content profile module and the problem fixed.
Thanks

Comment #6

19 July 2009 at 12:00

Status:

Fixed

» Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

Relationship based on another relationship

Comments

Comment #1

Comment #2

Comment #3

Comment #4

Comment #5

Comment #6

News items

Our community

Documentation

Drupal code base

Governance of community