I have a view where I know the user ID and I need to grab the contact associated with the user. When I add the Contact: User Contact (The contact associated with this user via redhen_contact_user) relationship, I get an error when trying to display data in my view:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'redhen_contact_redhen_contact_user.status' in 'on clause'

My SQL statement is:

SELECT commerce_order.order_number AS commerce_order_order_number, commerce_order.order_id AS order_id, commerce_order.uid AS commerce_order_uid, commerce_order.created AS commerce_order_created, commerce_order.changed AS commerce_order_changed, commerce_order.status AS commerce_order_status, 'commerce_order' AS field_data_commerce_order_total_commerce_order_entity_type
FROM
{commerce_order} commerce_order
LEFT JOIN {users} users_commerce_order ON commerce_order.uid = users_commerce_order.uid
LEFT JOIN {redhen_contact_user} users_commerce_order__redhen_contact_user ON users_commerce_order.uid = users_commerce_order__redhen_contact_user.uid AND users_commerce_order__redhen_contact_user.status = '1'
LEFT JOIN {redhen_contact} redhen_contact_redhen_contact_user ON users_commerce_order__redhen_contact_user.contact_id = redhen_contact_redhen_contact_user.contact_id AND redhen_contact_redhen_contact_user.status = '1'
WHERE (( (redhen_contact_redhen_contact_user.contact_id = '7001' ) )AND(( (commerce_order.status NOT IN ('cart', 'checkout_registration', 'checkout_checkout', 'checkout_review', 'checkout_payment', 'checkout_complete')) )))
ORDER BY commerce_order_order_number DESC

If I comment out line 73 of modules/redhen_contact/lib/redhen_contact_views.inc:

array('field' => 'status', 'value' => '1', 'operator' => '='),

my view displays the correct data (i.e. only data where status = 1) and the error goes away.

Thanks,
Mickey

Comments

seanberto’s picture

Hi Mickey, is this still an issue? If so, which release of the module? And do you mind posting your Views definition so that we can test locally?

micnap’s picture

Hi Sean,

This was with version 7.x-1.0+16-dev. Looks like there's been a few updates!

I should be getting back into this project later this week. I'll update to the latest and post back.

Thanks!
Mickey

micnap’s picture

Finally got back to this and updated to the latest dev version. It is still an issue. Looking at the sql query, line 73 of modules/redhen_contact/lib/redhen_contact_views.inc causes the view to look for a status field in the redhen_contact table which doesn't exist:

LEFT JOIN {redhen_contact} redhen_contact_redhen_contact_user ON users_commerce_order__redhen_contact_user.contact_id = redhen_contact_redhen_contact_user.contact_id AND redhen_contact_redhen_contact_user.status = '1'

Here's an export of my view. It's the last display (User Orders displayed with Redhen contact ) that has the problem:

$view = new view();
$view->name = 'commerce_user_orders';
$view->description = 'Display a list of completed orders for a user.';
$view->tag = 'commerce';
$view->base_table = 'commerce_order';
$view->human_name = 'User orders';
$view->core = 7;
$view->api_version = '3.0';
$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['title'] = 'Orders';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['access']['perm'] = 'view own commerce_order entities';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['query']['options']['query_comment'] = FALSE;
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['pager']['options']['items_per_page'] = 25;
$handler->display->display_options['style_plugin'] = 'table';
$handler->display->display_options['style_options']['columns'] = array(
'order_number' => 'order_number',
'created' => 'created',
'changed' => 'changed',
'commerce_order_total' => 'commerce_order_total',
'status' => 'status',
);
$handler->display->display_options['style_options']['default'] = 'order_number';
$handler->display->display_options['style_options']['info'] = array(
'order_number' => array(
'sortable' => 1,
'default_sort_order' => 'desc',
'align' => '',
'separator' => '',
'empty_column' => 0,
),
'created' => array(
'sortable' => 1,
'default_sort_order' => 'desc',
'align' => '',
'separator' => '',
'empty_column' => 0,
),
'changed' => array(
'sortable' => 1,
'default_sort_order' => 'desc',
'align' => '',
'separator' => '',
'empty_column' => 0,
),
'commerce_order_total' => array(
'sortable' => 0,
'default_sort_order' => 'asc',
'align' => '',
'separator' => '',
'empty_column' => 0,
),
'status' => array(
'sortable' => 1,
'default_sort_order' => 'asc',
'align' => '',
'separator' => '',
'empty_column' => 0,
),
);
/* No results behavior: Global: Text area */
$handler->display->display_options['empty']['text']['id'] = 'text';
$handler->display->display_options['empty']['text']['table'] = 'views';
$handler->display->display_options['empty']['text']['field'] = 'area';
$handler->display->display_options['empty']['text']['content'] = 'You have not placed any orders with us yet.';
$handler->display->display_options['empty']['text']['format'] = 'plain_text';
/* Field: Commerce Order: Order number */
$handler->display->display_options['fields']['order_number']['id'] = 'order_number';
$handler->display->display_options['fields']['order_number']['table'] = 'commerce_order';
$handler->display->display_options['fields']['order_number']['field'] = 'order_number';
$handler->display->display_options['fields']['order_number']['link_to_order'] = 'customer';
/* Field: Commerce Order: Created date */
$handler->display->display_options['fields']['created']['id'] = 'created';
$handler->display->display_options['fields']['created']['table'] = 'commerce_order';
$handler->display->display_options['fields']['created']['field'] = 'created';
$handler->display->display_options['fields']['created']['label'] = 'Created';
/* Field: Commerce Order: Updated date */
$handler->display->display_options['fields']['changed']['id'] = 'changed';
$handler->display->display_options['fields']['changed']['table'] = 'commerce_order';
$handler->display->display_options['fields']['changed']['field'] = 'changed';
/* Field: Commerce Order: Order total */
$handler->display->display_options['fields']['commerce_order_total']['id'] = 'commerce_order_total';
$handler->display->display_options['fields']['commerce_order_total']['table'] = 'field_data_commerce_order_total';
$handler->display->display_options['fields']['commerce_order_total']['field'] = 'commerce_order_total';
$handler->display->display_options['fields']['commerce_order_total']['label'] = 'Total';
$handler->display->display_options['fields']['commerce_order_total']['click_sort_column'] = 'amount';
$handler->display->display_options['fields']['commerce_order_total']['type'] = 'commerce_price_formatted_amount';
$handler->display->display_options['fields']['commerce_order_total']['settings'] = array(
'calculation' => FALSE,
);
/* Field: Commerce Order: Order status */
$handler->display->display_options['fields']['status']['id'] = 'status';
$handler->display->display_options['fields']['status']['table'] = 'commerce_order';
$handler->display->display_options['fields']['status']['field'] = 'status';
/* Contextual filter: Commerce Order: Uid */
$handler->display->display_options['arguments']['uid_1']['id'] = 'uid_1';
$handler->display->display_options['arguments']['uid_1']['table'] = 'commerce_order';
$handler->display->display_options['arguments']['uid_1']['field'] = 'uid';
$handler->display->display_options['arguments']['uid_1']['default_action'] = 'not found';
$handler->display->display_options['arguments']['uid_1']['default_argument_type'] = 'fixed';
$handler->display->display_options['arguments']['uid_1']['summary']['number_of_records'] = '0';
$handler->display->display_options['arguments']['uid_1']['summary']['format'] = 'default_summary';
$handler->display->display_options['arguments']['uid_1']['summary_options']['items_per_page'] = '25';
$handler->display->display_options['arguments']['uid_1']['specify_validation'] = TRUE;
$handler->display->display_options['arguments']['uid_1']['validate']['type'] = 'current_user_or_role';
$handler->display->display_options['arguments']['uid_1']['validate_options']['restrict_roles'] = TRUE;
$handler->display->display_options['arguments']['uid_1']['validate_options']['roles'] = array(
3 => '3',
);
/* Filter criterion: Commerce Order: Order state */
$handler->display->display_options['filters']['state']['id'] = 'state';
$handler->display->display_options['filters']['state']['table'] = 'commerce_order';
$handler->display->display_options['filters']['state']['field'] = 'state';
$handler->display->display_options['filters']['state']['operator'] = 'not in';
$handler->display->display_options['filters']['state']['value'] = array(
'cart' => 'cart',
'checkout' => 'checkout',
);

/* Display: User Orders */
$handler = $view->new_display('page', 'User Orders', 'order_page');
$handler->display->display_options['defaults']['hide_admin_links'] = FALSE;
$handler->display->display_options['path'] = 'user/%/orders';
$handler->display->display_options['menu']['type'] = 'tab';
$handler->display->display_options['menu']['title'] = 'Orders';
$handler->display->display_options['menu']['weight'] = '15';
$handler->display->display_options['tab_options']['type'] = 'normal';
$handler->display->display_options['tab_options']['title'] = 'Orders';
$handler->display->display_options['tab_options']['description'] = 'User orders in the store.';
$handler->display->display_options['tab_options']['weight'] = '';
$handler->display->display_options['tab_options']['name'] = 'user-menu';

/* Display: User Orders displayed with Redhen contact */
$handler = $view->new_display('page', 'User Orders displayed with Redhen contact', 'page_1');
$handler->display->display_options['defaults']['hide_admin_links'] = FALSE;
$handler->display->display_options['defaults']['relationships'] = FALSE;
/* Relationship: Commerce Order: Owner */
$handler->display->display_options['relationships']['uid']['id'] = 'uid';
$handler->display->display_options['relationships']['uid']['table'] = 'commerce_order';
$handler->display->display_options['relationships']['uid']['field'] = 'uid';
/* Relationship: Contact: User Contact */
$handler->display->display_options['relationships']['contact_id']['id'] = 'contact_id';
$handler->display->display_options['relationships']['contact_id']['table'] = 'redhen_contact_user';
$handler->display->display_options['relationships']['contact_id']['field'] = 'contact_id';
$handler->display->display_options['relationships']['contact_id']['relationship'] = 'uid';
$handler->display->display_options['defaults']['arguments'] = FALSE;
/* Contextual filter: Contact: Contact ID */
$handler->display->display_options['arguments']['contact_id']['id'] = 'contact_id';
$handler->display->display_options['arguments']['contact_id']['table'] = 'redhen_contact';
$handler->display->display_options['arguments']['contact_id']['field'] = 'contact_id';
$handler->display->display_options['arguments']['contact_id']['relationship'] = 'contact_id';
$handler->display->display_options['arguments']['contact_id']['default_argument_type'] = 'fixed';
$handler->display->display_options['arguments']['contact_id']['summary']['number_of_records'] = '0';
$handler->display->display_options['arguments']['contact_id']['summary']['format'] = 'default_summary';
$handler->display->display_options['arguments']['contact_id']['summary_options']['items_per_page'] = '25';
$handler->display->display_options['defaults']['filter_groups'] = FALSE;
$handler->display->display_options['defaults']['filters'] = FALSE;
/* Filter criterion: Commerce Order: Order state */
$handler->display->display_options['filters']['state']['id'] = 'state';
$handler->display->display_options['filters']['state']['table'] = 'commerce_order';
$handler->display->display_options['filters']['state']['field'] = 'state';
$handler->display->display_options['filters']['state']['operator'] = 'not in';
$handler->display->display_options['filters']['state']['value'] = array(
'cart' => 'cart',
'checkout' => 'checkout',
);
$handler->display->display_options['path'] = 'redhen/contact/%/orders';
$handler->display->display_options['menu']['type'] = 'tab';
$handler->display->display_options['menu']['title'] = 'Orders';
$handler->display->display_options['menu']['weight'] = '15';
$handler->display->display_options['tab_options']['type'] = 'normal';
$handler->display->display_options['tab_options']['title'] = 'Orders';
$handler->display->display_options['tab_options']['description'] = 'User orders in the store.';
$handler->display->display_options['tab_options']['weight'] = '';
$handler->display->display_options['tab_options']['name'] = 'user-menu';

Thanks!
Mickey

levelos’s picture

Status: Active » Fixed

Thanks Mickey! Should be all set in 4f273ff095601ecc63749cee9e4ef22ea4805c4e.

Status: Fixed » Closed (fixed)

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

  • Commit 4f273ff on 7.x-1.x, tests, redhen-donation by levelos:
    #1953494 by levelos: Resolve error when creating views relationships...

  • Commit 4f273ff on 7.x-1.x, tests, redhen-donation, relation-roles by levelos:
    #1953494 by levelos: Resolve error when creating views relationships...