Unknown column 'civicrm_uf_match2.uf_id' in Drupal Views

[tetsuo]

Hi,

I'm trying to create a view that will show some information related to the user's Drupal profile.
In a later phase I would like to have a panel on every Drupal users' profile page which would contain a view that contains some related data from the CiviCRM system.

Right now I'm stuck at creating a view and I really don't know where the mistake might be.
I'm getting the following SQL syntax error:

Code: [Select]

user warning: Unknown column 'civicrm_uf_match2.uf_id' in 'on clause' query:
SELECT
    DISTINCT(users.uid)
AS
    uid, users_civicrm_uf_match2__civicrm_contact.display_name_da_DK
AS
    users_civicrm_uf_match2__civicrm_contact_display_name_da_DK, users_civicrm_uf_match2__civicrm_contact.id
AS
    users_civicrm_uf_match2__civicrm_contact_id, users_civicrm_uf_match2__civicrm_contact.last_name_da_DK
AS
    users_civicrm_uf_match2__civicrm_contact_last_name_da_DK, users_civicrm_uf_match2__civicrm_address.street_address
AS
    users_civicrm_uf_match2__civicrm_address_street_address
FROM
    users users
    LEFT JOIN
        lydtek_civicrm.civicrm_uf_match civicrm_uf_match
    ON
        users.uid = civicrm_uf_match.uf_id
    INNER JOIN
        lydtek_civicrm.civicrm_contact civicrm_uf_match2
    ON
        civicrm_uf_match.contact_id = civicrm_uf_match2.id
    INNER JOIN
        users users_civicrm_uf_match2
    ON
        civicrm_uf_match2.uf_id = users_civicrm_uf_match2.uid
    LEFT JOIN
        lydtek_civicrm.civicrm_uf_match users_civicrm_uf_match2__civicrm_uf_match
    ON
        users_civicrm_uf_match2.uid = users_civicrm_uf_match2__civicrm_uf_match.uf_id
    LEFT JOIN
        lydtek_civicrm.civicrm_uf_match users_civicrm_uf_match2_users_civicrm_uf_match2__civicrm_uf_match
    ON
        users_civicrm_uf_match2.uid = users_civicrm_uf_match2_users_civicrm_uf_match2__civicrm_uf_match.uf_id
    LEFT JOIN
        lydtek_civicrm.civicrm_contact users_civicrm_uf_match2__civicrm_contact
    ON
        users_civicrm_uf_match2__civicrm_uf_match.contact_id = users_civicrm_uf_match2__civicrm_contact.id
    LEFT JOIN lydtek_civicrm.civicrm_address users_civicrm_uf_match2__civicrm_address
        ON users_civicrm_uf_match2__civicrm_uf_match.contact_id = users_civicrm_uf_match2__civicrm_address.contact_id LIMIT 0, 10

in /data/www/html/lydtek/sites/all/modules/views/includes/view.inc on line 755.

Information about the view and the installation:

CiviCRM at version 2.2.8
Drupal at 6.10
I am running an internationalized version of CiviCRM.

1) Normal User view
2) View export dump:

Code: [Select]

$view = new view;
$view->name = 'profile_civicrm_record';
$view->description = 'Detailed user and related company information';
$view->tag = '';
$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(
  'drupal_id' => array(
    'label' => 'Drupal User',
    'required' => 1,
    'id' => 'drupal_id',
    'table' => 'civicrm_contact',
    'field' => 'drupal_id',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('fields', array(
  'display_name_da_DK' => array(
    'label' => 'Display Name',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'link_class' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      'html' => 0,
    ),
    'link_to_civicrm_contact' => 0,
    'exclude' => 0,
    'id' => 'display_name_da_DK',
    'table' => 'civicrm_contact',
    'field' => 'display_name_da_DK',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'drupal_id',
  ),
  'last_name_da_DK' => array(
    'label' => 'Last Name',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'link_class' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      'html' => 0,
    ),
    'link_to_civicrm_contact' => 0,
    'exclude' => 0,
    'id' => 'last_name_da_DK',
    'table' => 'civicrm_contact',
    'field' => 'last_name_da_DK',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'drupal_id',
  ),
  'street_address' => array(
    'label' => 'Full Street Address',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'link_class' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      'html' => 0,
    ),
    'exclude' => 0,
    'id' => 'street_address',
    'table' => 'civicrm_address',
    'field' => 'street_address',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'drupal_id',
  ),
));
$handler->override_option('arguments', array(
  'id' => array(
    'default_action' => 'ignore',
    'style_plugin' => 'default_summary',
    'style_options' => array(),
    'wildcard' => 'all',
    'wildcard_substitution' => 'All',
    'title' => '',
    'breadcrumb' => '',
    'default_argument_type' => 'fixed',
    'default_argument' => '',
    'validate_type' => 'none',
    'validate_fail' => 'not found',
    'break_phrase' => 0,
    'not' => 0,
    'id' => 'id',
    'table' => 'civicrm_contact',
    'field' => 'id',
    'validate_user_argument_type' => 'uid',
    'validate_user_roles' => array(
      '2' => 0,
      '4' => 0,
      '6' => 0,
      '8' => 0,
      '5' => 0,
      '10' => 0,
      '7' => 0,
      '3' => 0,
      '9' => 0,
    ),
    'relationship' => 'drupal_id',
    'default_options_div_prefix' => '',
    'default_argument_user' => 0,
    'default_argument_fixed' => '',
    'default_argument_php' => '',
    'validate_argument_node_type' => array(
      'webform' => 0,
      'blog' => 0,
      'poll' => 0,
      'image' => 0,
      'forum' => 0,
      'panel' => 0,
      'book' => 0,
      'event' => 0,
      'feed' => 0,
      'feed_event' => 0,
      'group' => 0,
      'grouppost' => 0,
      'icalfeed' => 0,
      'page' => 0,
      'simplenews' => 0,
      'story' => 0,
      'uprofile' => 0,
      'wiki' => 0,
    ),
    'validate_argument_node_access' => 0,
    'validate_argument_nid_type' => 'nid',
    'validate_argument_vocabulary' => array(
      '1' => 0,
      '3' => 0,
      '2' => 0,
    ),
    'validate_argument_type' => 'tid',
    'validate_argument_transform' => 0,
    'validate_user_restrict_roles' => 0,
    'image_size' => array(
      '_original' => '_original',
      'thumbnail' => 'thumbnail',
      'preview' => 'preview',
    ),
    'validate_argument_is_member' => 0,
    'validate_argument_php' => '',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('cache', array(
  'type' => 'none',
));
$handler->override_option('distinct', 1);
$handler = $view->new_display('panel_pane', 'Content pane', 'panel_pane_1');
$handler->override_option('pane_title', '');
$handler->override_option('pane_description', '');
$handler->override_option('pane_category', array(
  'name' => 'View panes',
  'weight' => 0,
));
$handler->override_option('allow', array(
  'use_pager' => FALSE,
  'items_per_page' => FALSE,
  'offset' => FALSE,
  'link_to_view' => FALSE,
  'more_link' => FALSE,
  'path_override' => FALSE,
  'title_override' => FALSE,
  'exposed_form' => FALSE,
));
$handler->override_option('argument_input', array(
  'id' => array(
    'type' => 'context',
    'context' => 'node.uid',
    'panel' => '0',
    'fixed' => '',
    'label' => 'CiviCRM Contacts: Contact ID',
  ),
));
$handler->override_option('link_to_view', 0);
$handler->override_option('inherit_panels_path', 0);


Any help / tips / hints are greatly appreciated!
Cheers!

[jalama]

what other modules are you running, panels?

[tetsuo]

Here is a list of other modules that run with this setup (that's a dump from the database table 'system'):

addtoany
better_messages
system
aggregator
block
blog
book
color
comment
contact
dblog
filter
forum
help
locale
menu
node
path
php
ping
poll
profile
search
statistics
syslog
taxonomy
throttle
tracker
translation
trigger
update
upload
user
blocks404
captcha
recaptcha
recaptcha_mailhide
securepages
securepages_prevent_hijack
filefield
contemplate
content
content_copy
content_permissions
fieldgroup
image
image_attach
image_gallery
image_im_advanced
image_import
imageapi
imageapi_gd
imageapi_imagemagick
imagecache
imagecache_canvasactions
imagecache_coloractions
imagecache_customactions
imagecache_profiles
imagecache_textactions
imagecache_ui
imagefield
imagefield_crop
imce
imce_wysiwyg
imceimage
img_assist
inline
jquery_aop
mimemail
mimemail_compress
nodereference
number
optionwidgets
simplenews
simplenews_action
simplenews_analytics
simplenews_digest
simplenews_register
simplenews_statistics
text
transliteration
userreference
watcher
wysiwyg
filefield_meta
lightbox2
og
og_access
og_actions
og_notifications
og_views
simpleviews
token
token_actions
views
views_export
views_ui
views_content
advanced_help
getid3
diff
wikitools
flexifilter
freelinking
wysiwyg_filter
headinganchors
tableofcontents
talk
autologout
messaging
messaging_mime_mail
messaging_notify
messaging_phpmailer
messaging_privatemsg
messaging_simple
notifications
notifications_autosubscribe
notifications_content
notifications_custom
notifications_feed
notifications_lite
notifications_tags
notifications_ui
notifications_views
civicrm
civicrm_eventblock
civicrm_group_roles
civicrm_member_roles
civicrm_og_sync
civicrm_theme
og_expire
google
google_cse
jquery_ui
checkall
currency
currency_api
date
date_api
date_locale
date_repeat
date_timezone
date_tools
format_number
formatted_number
link
money
money_conversion_dialog
calendar
calendar_ical
jcalendar
jquery_update
acl
content_access
rules
rules_admin
rules_scheduler
webform
feedapi
feedapi_inherit
feedapi_mapper
feedapi_node
parser_common_syndication
parser_ical
flowplayer
flowplayer3
imagerotator
lutman
mimedetect
onepixelout
simpleviewer
swfobject
swfobject2
swftools
ufo
wijering
wijering4
image_fupload
image_fupload_image
image_fupload_imagefield
spamspan
tokenSTARTER
og_vocab
og_subgroups
timeline
ctools
delegator
panels
panels_mini
panels_node
pathauto
type_user_nids
ie6ban
i18n
i18nblocks
i18ncontent
i18nmenu
i18npoll
i18nprofile
i18nstrings
i18nsync
i18ntaxonomy
languageicons
translation_overview
translation_table
email_verify
teleport
checkbox_validate
i18nviews
content_profile
googleanalytics
garland
admin_menu
forum_access
content_profile_registration
auto_nodetitle
gmap
gmap_macro_builder
gmap_taxonomy
fivestar
fivestar_comment
myaccount_alter
votingapi
xmlsitemap
bulk_export
page_manager
autocomplete_widgets
xmlsitemap_engines
xmlsitemap_node
xmlsitemap_taxonomy
og_forum
site_map
profileplus
apachesolr
apachesolr_image
apachesolr_nodeaccess
apachesolr_og
apachesolr_search
advanced_profile
author_pane
cck_field_privacy
pm_block_user
pm_email_notify
privatemsg
privatemsg_filter
user_relationship_blocks
user_relationship_defaults
user_relationship_elaborations
user_relationship_implications
user_relationship_invites
user_relationship_mailer
user_relationship_migrate
user_relationship_node_access
user_relationship_views
user_relationships_api
user_relationships_ui
invite
invite_stats
jquery_impromptu
better_formats
civicrm_cck
civicrm_cck_activity

[jalama]

So the issue is in the relationship to the CiviCRM Contacts: Drupal ID field.  I don't know exactly what it is but it occured to me I don't know why that relationship is there other than to allow a view to filter for the existence of a value in the drupal_id field in the civicrm_uf_match table.  Something that is more elegantly accomplished by adding a filter to the drupal_id field. That can be accomplished by pasting the following code in at line 141 of the civicrm.views.inc file in <drupal modules folder>/civicrm/drupal/modules/views/civicrm.views.inc.  In version 2.2.8.

Am I missing something here?  If not I'll submit a patch to dump that relationship.

Code: [Select]

                                                  'filter' => array(
                                                              'handler' => 'views_handler_filter_numeric',
                                                              ),

so the druaplid field looks like this

Code: [Select]

    // Drupal ID of the contact (from uf_match.uf_id)   
    $data['civicrm_contact']['drupal_id'] = array( 
                                                  'real field' => 'id',
                                                  'title' => t('Drupal ID'),
                                                  'help' => t('The drupal ID.'),
                                                  'field' => array(
                                                                   'handler' => 'civicrm_handler_field_drupalid',
                                                                   'click sortable' => TRUE,
                                                                   ),
                                                  'sort' => array(
                                                                  'handler' => 'views_handler_sort',
                                                                  ),
                                                  'filter' => array(
                                                              'handler' => 'views_handler_filter_numeric',
                                                              ),
                                                  'relationship' => array(
                                                                          'base' => 'users',
                                                                          'field' => 'uid',
                                                                          'handler' => 'civicrm_handler_relationship_contact2users',
                                                                          'label' => t('Drupal User'),
                                                                          ),
                                                    );


[petednz]

Hitting a similar error while investigating if Relationship - CiviCRM Contacts: Drupal ID is part of what I need. What was your conclusion Jim?

user warning: Unknown column 'civicrm_uf_match2.uf_id' in 'on clause' query: SELECT users.uid AS uid, users_civicrm_uf_match2.name AS users_civicrm_uf_match2_name, users_civicrm_uf_match2.uid AS users_civicrm_uf_match2_uid FROM drupal6_users users LEFT JOIN civicrm_uf_match civicrm_uf_match ON users.uid = civicrm_uf_match.uf_id INNER JOIN civicrm_contact civicrm_uf_match2 ON civicrm_uf_match.contact_id = civicrm_uf_match2.id INNER JOIN drupal6_users users_civicrm_uf_match2 ON civicrm_uf_match2.uf_id = users_civicrm_uf_match2.uid LIMIT 0, 20 in /export/home/www/drupal6/sites/maawg/modules/views/includes/view.inc on line 755.

[petednz]

UPdating - to get what I want ie a view that shows info just for logged in user it seems i have to apply either 'argument = user uid = default argument = 'user id from logged in user' - which means it looks like i have to use the 'view type = User' - which i think means i need to be able to use 'relationship CiviCRM Contacts: Drupal ID' but that is what generates above error.

[petednz]

So, I applied the patch at the point where I had the following set up in a View = Type User

Relationship = CiviCRM Contacts: Drupal ID
Fields = CiviCRM Contacts: Contact ID

But when I then added

Argument = User:Uid (default argument = User ID from logged in user)

I got this error

user warning: Unknown column 'civicrm_uf_match2.uf_id' in 'on clause' query: SELECT users.uid AS uid, civicrm_contact.id AS civicrm_contact_id FROM users users LEFT JOIN civicrm_uf_match civicrm_uf_match ON users.uid = civicrm_uf_match.uf_id LEFT JOIN civicrm_contact civicrm_uf_match2 ON civicrm_uf_match.contact_id = civicrm_uf_match2.id LEFT JOIN users users_civicrm_uf_match2 ON civicrm_uf_match2.uf_id = users_civicrm_uf_match2.uid LEFT JOIN civicrm_contact civicrm_contact ON civicrm_uf_match.contact_id = civicrm_contact.id WHERE users.uid = 1 LIMIT 0, 10 in /Users/pd/Sites/drupal-6.11/sites/all/modules/views/includes/view.inc on line 731.

So not sure if I haven't implemented the patch properly or there is more to this issue. (and not sure it will solve my dilemma anyhow but worth trying)

[jalama]

Yea, at the NYC dev camp I started to get a handle on this one.  I need to look at it more and take a bigger look at how relationships are best implemented and used with CiviCRM.

Basically the only place the work now is in Contact views.

[jalama]

okay I think this patch fixes it

http://issues.civicrm.org/jira/browse/CRM-5087.

let me know

[petednz]

hey macho thanks - will try and test it this week

[mariagwyn]

I applied this patch a while ago, and it has been working beautifully.  Now, eager to do even more on my site, I created a Contribution View, hoping to show a list of member contributions, limited to the drupal logged in user.  No go.  Same error as above. 

The drupal ID shows up as a field, but it does not show up as either an argument or a filter.  And once I add the relationship, the error appears.

After reviewing this post again, I noticed that this patch is really for Contact views, not contributions.  Any way to make this available to a wider range of views?