Relationship based on another relationship
masood_mj - June 12, 2009 - 11:52
| Project: | Views |
| Version: | 6.x-2.6 |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | closed |
Jump to:
Description
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',
));
#1
bump!
#2
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.
#3
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.
#4
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.
#5
I have updated content profile module and the problem fixed.
Thanks
#6
Automatically closed -- issue fixed for 2 weeks with no activity.