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
Comment #1
seanberto CreditAttribution: seanberto commentedHi 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?
Comment #2
micnap CreditAttribution: micnap commentedHi 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
Comment #3
micnap CreditAttribution: micnap commentedFinally 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
Comment #4
levelos CreditAttribution: levelos commentedThanks Mickey! Should be all set in 4f273ff095601ecc63749cee9e4ef22ea4805c4e.