Problem with queries with the same field repeated

Thanks a lot for the quickly reply MerlinOfChaos,
but this is exactly what I'm doing. In fact all works correctly if I view a field of teacher_t other of first_name or last_name (i.e. a field that isn't present also in student_t).
The problem arises only if I try to view a field that is present identically in student_t and teacher_t.
I have tried a lot of views customizations to resolve the problem, but without results.
The last (the one permits me to see correctly other fields of teacher_t, but first_name and last_name) doing this:
The view is built on users. I did a relationship with custom_profile of type student_t (named student). On this I build a relationship with user_reference (named tutor). On this I build a relationship with custom_profile of type teacher_t (named teacher). With this view I can do the the above:
For example:
- student John Green class 1st --> tutor: Mark Brown matter Math
The wanted table is:
Student First name Student Last name Student Class Tutor First name Tutor Last name Tutor Matter
John Green 1st Mark Brown Math
The obtained table is always:
Student First name Student Last name Student Class Tutor First name Tutor Last name Tutor Matter
John Green 1st John Green Math
So the problem regards only fields with the same name on on the two type of nodes student_t and teacher_t (instead the two fields "student_class" and "teacher_matter" didn't create any problem and working correctly).
Thanks a lot!
Regards,
Saxx

Hi,
I have tested the last dev version but the problem is not solved.
I'll try to recreate the problem with a fresh minimal installation and eventually I'll post more informations about this.
Thanks a lot.
Saxx

Hi merlin,
I have recreated the problem on a fresh minimal installation (drupal 6.9, cck 2.1, content_profile 1.0-beta3, views 2.x-dev).
The configuration are the same presented before:
- one content type student_t (content_profile) with fields field_first_name, field_last_name, field_class, field_tutor (user reference)
- one content type teacher_t (content_profile) with fields field_first_name, field_last_name, field_matter
I have populated the db with four users, two students (Martha White and Charles Black) and two teachers (John Green and Mark Brown).
I have created the view as described in previous post (and coherent with your suggestions) and the result is the same previously cited.
I attach the screenshot of the table resulting and I post above the query and the exported view.
QUERY

SELECT users.uid AS uid,
   node_data_field_first_name.field_first_name_value AS node_data_field_first_name_field_first_name_value,
   node_data_field_first_name.nid AS node_data_field_first_name_nid,
   node_users.type AS node_users_type,
   node_data_field_last_name.field_last_name_value AS node_data_field_last_name_field_last_name_value,
   node_data_field_last_name.nid AS node_data_field_last_name_nid,
   node_data_field_class.field_class_value AS node_data_field_class_field_class_value,
   node_data_field_class.nid AS node_data_field_class_nid,
   node_users_1.type AS node_users_1_type,
   node_data_field_matter.field_matter_value AS node_data_field_matter_field_matter_value,
   node_data_field_matter.nid AS node_data_field_matter_nid
 FROM users users 
 INNER JOIN node node_users ON users.uid = node_users.uid AND node_users.type = 'student_t'
 LEFT JOIN content_type_student_t node_data_field_tutor ON node_users.vid = node_data_field_tutor.vid
 LEFT JOIN users users_node_data_field_tutor ON node_data_field_tutor.field_tutor_uid = users_node_data_field_tutor.uid
 LEFT JOIN node node_users_1 ON users_node_data_field_tutor.uid = node_users_1.uid AND node_users_1.type = 'teacher_t'
 LEFT JOIN content_field_first_name node_data_field_first_name ON node_users.vid = node_data_field_first_name.vid
 LEFT JOIN content_field_last_name node_data_field_last_name ON node_users.vid = node_data_field_last_name.vid
 LEFT JOIN content_type_student_t node_data_field_class ON node_users.vid = node_data_field_class.vid
 LEFT JOIN content_type_teacher_t node_data_field_matter ON node_users_1.vid = node_data_field_matter.vid

EXPORTED VIEW

$view = new view;
$view->name = 'Tutoring';
$view->description = 'Tutoring';
$view->tag = 'tutoring';
$view->view_php = '';
$view->base_table = 'users';
$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(
  'content_profile_rel' => array(
    'label' => 'Student',
    'required' => 1,
    'type' => 'student_t',
    'id' => 'content_profile_rel',
    'table' => 'users',
    'field' => 'content_profile_rel',
    'relationship' => 'none',
  ),
  'field_tutor_uid' => array(
    'label' => 'Tutor',
    'required' => 0,
    'delta' => -1,
    'id' => 'field_tutor_uid',
    'table' => 'node_data_field_tutor',
    'field' => 'field_tutor_uid',
    'relationship' => 'content_profile_rel',
  ),
  'content_profile_rel_1' => array(
    'label' => 'Teacher',
    'required' => 0,
    'type' => 'teacher_t',
    'id' => 'content_profile_rel_1',
    'table' => 'users',
    'field' => 'content_profile_rel',
    'relationship' => 'field_tutor_uid',
  ),
));
$handler->override_option('fields', array(
  'field_first_name_value' => array(
    'label' => 'First name student',
    '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' => 1,
    'label_type' => 'custom',
    'format' => 'default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_first_name_value',
    'table' => 'node_data_field_first_name',
    'field' => 'field_first_name_value',
    'relationship' => 'content_profile_rel',
  ),
  'field_last_name_value' => array(
    'label' => 'Last name student',
    '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,
    'label_type' => 'custom',
    'format' => 'default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_last_name_value',
    'table' => 'node_data_field_last_name',
    'field' => 'field_last_name_value',
    'relationship' => 'content_profile_rel',
  ),
  'field_class_value' => array(
    'label' => 'Class student',
    '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,
    'label_type' => 'custom',
    'format' => 'default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_class_value',
    'table' => 'node_data_field_class',
    'field' => 'field_class_value',
    'relationship' => 'content_profile_rel',
  ),
  'field_first_name_value_1' => array(
    'label' => 'First name teacher',
    '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' => 1,
    'label_type' => 'custom',
    'format' => 'default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_first_name_value_1',
    'table' => 'node_data_field_first_name',
    'field' => 'field_first_name_value',
    'relationship' => 'content_profile_rel_1',
  ),
  'field_last_name_value_1' => array(
    'label' => 'Last name teacher',
    '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,
    'label_type' => 'custom',
    'format' => 'default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_last_name_value_1',
    'table' => 'node_data_field_last_name',
    'field' => 'field_last_name_value',
    'relationship' => 'content_profile_rel_1',
  ),
  'field_matter_value' => array(
    'label' => 'Matter teacher',
    '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,
    'label_type' => 'custom',
    'format' => 'default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_matter_value',
    'table' => 'node_data_field_matter',
    'field' => 'field_matter_value',
    'relationship' => 'content_profile_rel_1',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('style_plugin', 'table');

Thanks a lot for your attention. If you need some other information or tests, tell me.
Best regards,
Saxx

Hi merlin,
I have tried to define two new node types: s_t (type for student with first_name, last_name, class and a node_reference to a t_t type) and t_t (type for teacher with first_name, last_name and matter).
I have populated four nodes with the same values of the previous post (two student and two teachers) and I have created a new view (based on nodes) with "the same logic" of the previous (but this time I need obviously only one relationship, based on nodereference).
This is the query:
QUERY

SELECT node.nid AS nid,
   node_data_field_first_name.field_first_name_value AS node_data_field_first_name_field_first_name_value,
   node_data_field_first_name.nid AS node_data_field_first_name_nid,
   node.type AS node_type,
   node_data_field_last_name.field_last_name_value AS node_data_field_last_name_field_last_name_value,
   node_data_field_last_name.nid AS node_data_field_last_name_nid,
   node_data_field_class.field_class_value AS node_data_field_class_field_class_value,
   node_data_field_class.nid AS node_data_field_class_nid,
   node_node_data_field_tutoring2.type AS node_node_data_field_tutoring2_type,
   node_data_field_matter.field_matter_value AS node_data_field_matter_field_matter_value,
   node_data_field_matter.nid AS node_data_field_matter_nid
 FROM node node 
 LEFT JOIN content_type_s_t node_data_field_tutoring2 ON node.vid = node_data_field_tutoring2.vid
 INNER JOIN node node_node_data_field_tutoring2 ON node_data_field_tutoring2.field_tutoring2_nid = node_node_data_field_tutoring2.nid
 LEFT JOIN content_field_first_name node_data_field_first_name ON node.vid = node_data_field_first_name.vid
 LEFT JOIN content_field_last_name node_data_field_last_name ON node.vid = node_data_field_last_name.vid
 LEFT JOIN content_field_class node_data_field_class ON node.vid = node_data_field_class.vid
 LEFT JOIN content_field_matter node_data_field_matter ON node_node_data_field_tutoring2.vid = node_data_field_matter.vid

and this is the exported view:
EXPORTED VIEW

$view = new view;
$view->name = 'Tutoring2';
$view->description = 'Tutoring without Content Profile';
$view->tag = 'tutoring2';
$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_tutoring2_nid' => array(
    'label' => 'Tutoring',
    'required' => 1,
    'delta' => -1,
    'id' => 'field_tutoring2_nid',
    'table' => 'node_data_field_tutoring2',
    'field' => 'field_tutoring2_nid',
    'relationship' => 'none',
  ),
));
$handler->override_option('fields', array(
  'field_first_name_value' => array(
    'label' => 'First name student',
    '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' => 1,
    'label_type' => 'custom',
    'format' => 'default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_first_name_value',
    'table' => 'node_data_field_first_name',
    'field' => 'field_first_name_value',
    'relationship' => 'none',
  ),
  'field_last_name_value' => array(
    'label' => 'Last name student',
    '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,
    'label_type' => 'custom',
    'format' => 'default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_last_name_value',
    'table' => 'node_data_field_last_name',
    'field' => 'field_last_name_value',
    'relationship' => 'none',
  ),
  'field_class_value' => array(
    'label' => 'Class',
    '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,
    'label_type' => 'widget',
    'format' => 'default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_class_value',
    'table' => 'node_data_field_class',
    'field' => 'field_class_value',
    'relationship' => 'none',
  ),
  'field_first_name_value_1' => array(
    'label' => 'First name teacher',
    '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,
    'label_type' => 'custom',
    'format' => 'default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_first_name_value_1',
    'table' => 'node_data_field_first_name',
    'field' => 'field_first_name_value',
    'relationship' => 'field_tutoring2_nid',
  ),
  'field_last_name_value_1' => array(
    'label' => 'Last name teacher',
    '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,
    'label_type' => 'custom',
    'format' => 'default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_last_name_value_1',
    'table' => 'node_data_field_last_name',
    'field' => 'field_last_name_value',
    'relationship' => 'field_tutoring2_nid',
  ),
  'field_matter_value' => array(
    'label' => 'Matter',
    '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,
    'label_type' => 'widget',
    'format' => 'default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_matter_value',
    'table' => 'node_data_field_matter',
    'field' => 'field_matter_value',
    'relationship' => 'field_tutoring2_nid',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('style_plugin', 'table');
$handler->override_option('style_options', array(
  'grouping' => '',
  'override' => 1,
  'sticky' => 0,
  'order' => 'asc',
  'columns' => array(
    'field_class_value' => 'field_class_value',
    'field_first_name_value' => 'field_first_name_value',
    'field_last_name_value' => 'field_last_name_value',
  ),
  'info' => array(
    'field_class_value' => array(
      'sortable' => 0,
      'separator' => '',
    ),
    'field_first_name_value' => array(
      'sortable' => 0,
      'separator' => '',
    ),
    'field_last_name_value' => array(
      'sortable' => 0,
      'separator' => '',
    ),
  ),
  'default' => '-1',
));

Unfortunately the result is the same! If you use the same field on each node type (s_t and t_t) the result (regarding this fields) are wrong. I attach also the image of the resulting table (identically to the previous one).
This test at least confirm us that this isn't a content_profile problem/bug.
If you have some other tests to do, tell me.
Thank you!
Best regards,
Saxx

Hi,
I have done some other tests, with other fields types and other views (with the same logic behind), but the result is still the same.
I think at this this problem can be targeted as a views bug, because the case of using the same field on different node types isn't so unusual (the cck interface is heavily projected to do this).
If you need some help on testing, tell me.
Thanks a lot.
Best regards,
Saxx

Thanks to you for the great work and this wonderful module!
As obviously, if you need some help (testing versions/patches) regarding this problem, tell me.
Best regards,
Saxx

Well, I have installed the last dev release (2009/03/26) on my test drupal installation (quite fresh), but the problem persists.
If you need some deeper test, tell me.
Thanks a lot!
Regards,
Saxx

Well, today I have no time to test it sufficiently. I have applied this patch on my local test site and the results are:
- the view with the use of content_type doesn't work well (fields of teacher_type are empty)
- the view with the use of only users works well (results are correct).
On the next days I'll test it more.
Thanks a lot,
Saxx

Well, I've tested this more.
I can confirm that for simple view (like post #7) this patch works well.
Instead for a more complicated view (like the original in post #5) the patch doesn't work. Really I think the problem is strictly related to the one I have posted on #433290: Fatal SQL errors caused by broken table aliases in some cases in add_relationships(). In this view in fact the problem is that the query is constructed bad and the table name in some joins are empty. I'm confidently that resolving the issue #433290: Fatal SQL errors caused by broken table aliases in some cases in add_relationships() can resolve indirectly also this problem.
As always, I'll test it more if you need it.
Thanks a lot and best regards,
Saxx

Hi,
I have tested last views version (2.5) with the suggested patch more deeply, using the last Content profile version (dev).
The view doesn't work correctly, but I've discovered the problem on the query. Above is the executed query (created by the views module):

SELECT users.uid AS uid,
   node_users_node_data_field_first_name.field_first_name_value AS node_users_node_data_field_first_name_field_first_name_value,
   node_users.nid AS node_users_nid,
   node_users.type AS node_users_type,
   node_users.vid AS node_users_vid,
   users_node_data_field_tutor.name AS users_node_data_field_tutor_name,
   users_node_data_field_tutor.uid AS users_node_data_field_tutor_uid,
   node_users_1_node_data_field_first_name.field_first_name_value AS node_users_1_node_data_field_first_name_field_first_name_value,
   node_users_1.nid AS node_users_1_nid,
   node_users_1.type AS node_users_1_type,
   node_users_1.vid AS node_users_1_vid
 FROM users users 
 LEFT JOIN node node_users ON users.uid = node_users.uid AND node_users.type = 'student_t'
 LEFT JOIN content_type_student_t node_users_node_data_field_tutor ON node_users.vid = node_users_node_data_field_tutor.vid
 INNER JOIN users users_node_data_field_tutor ON node_users_node_data_field_tutor.field_tutor_uid = users_node_data_field_tutor.uid
 LEFT JOIN node node_users_1 ON users.uid = node_users_1.uid AND node_users_1.type = 'teacher_t'
 LEFT JOIN content_field_first_name node_users_node_data_field_first_name ON node_users.vid = node_users_node_data_field_first_name.vid
 LEFT JOIN content_field_first_name node_users_1_node_data_field_first_name ON node_users_1.vid = node_users_1_node_data_field_first_name.vid

and above is the query modified "by hand" and tested on phpmyadmin:

SELECT users.uid AS uid,
   node_users_node_data_field_first_name.field_first_name_value AS node_users_node_data_field_first_name_field_first_name_value,
   node_users.nid AS node_users_nid,
   node_users.type AS node_users_type,
   node_users.vid AS node_users_vid,
   users_node_data_field_tutor.name AS users_node_data_field_tutor_name,
   users_node_data_field_tutor.uid AS users_node_data_field_tutor_uid,
   node_users_1_node_data_field_first_name.field_first_name_value AS node_users_1_node_data_field_first_name_field_first_name_value,
   node_users_1.nid AS node_users_1_nid,
   node_users_1.type AS node_users_1_type,
   node_users_1.vid AS node_users_1_vid
 FROM users users 
 LEFT JOIN node node_users ON users.uid = node_users.uid AND node_users.type = 'student_t'
 LEFT JOIN content_type_student_t node_users_node_data_field_tutor ON node_users.vid = node_users_node_data_field_tutor.vid
 INNER JOIN users users_node_data_field_tutor ON node_users_node_data_field_tutor.field_tutor_uid = users_node_data_field_tutor.uid
 LEFT JOIN node node_users_1 ON users_node_data_field_tutor.uid = node_users_1.uid AND node_users_1.type = 'teacher_t'
 LEFT JOIN content_field_first_name node_users_node_data_field_first_name ON node_users.vid = node_users_node_data_field_first_name.vid
 LEFT JOIN content_field_first_name node_users_1_node_data_field_first_name ON node_users_1.vid = node_users_1_node_data_field_first_name.vid

The difference is in line 16 where I have changed:
users.uid = node_users_1.uid
to:
users_node_data_field_tutor.uid = node_users_1.uid
This last query gives the correct result, where instead the first gives the wrong result.
Unfortunately I don't know where to watch (in the incredible views module code) to find the "code bug" and work to a patch. As always I can do all the tests you need to help you on this problem.
Thanks a lot and best regards,
Saxx

Problem fixed with latests views releases.