I've built a conference registration system using Commerce and Custom Line Item types, and I'm having trouble figuring out how to display my Custom Line Item fields in a View. Here's a short summary of what I've done:

  • The registration fields (e.g. name, email, etc.) are fields in a custom line item type.
  • My product view doubles as a registration form. It displays the fields of my custom line item type as part of the add to cart form. It also references a custom product type I created for the conference.
  • Some of the fields in the custom line type are booleans that trigger rules that add additional related products to the cart (e.g. hotel accommodations, meals, etc.).

I've built a View that summarizes the store's Orders, so that the conference organizer can see all of the Order info without having to know anything about Commerce or the Drupal admin pages. When I add the Line Items to this Order View, only the products show up (both the product referenced in my view, and those added by rules) - the other fields I've created for capturing the registration data (name, email, etc) don't show up.

I've also attempted to make a View that shows only Line Items (filtered to only my Custom Line Item Type), but nothing shows up and I get the following SQL error:

  • SQLSTATE[42S22]: Column not found: 1054 Unknown column 'commerce_line_item.uid' in 'field list'

Any help on getting my other Line Item fields to show up, or insight into this SQL error, would be MOST appreciated. Thanks!

(For some brief background, I have built other solutions using Commerce in conjunction with the Registrations module. This has caused a lot of problems, so I am attempting to recreate similar functionality without the Registration module)

  • whats the full SQL query being issued?
    – tenken
    Dec 2, 2013 at 22:03
  • Hi, your question actually led me to a solution - I removed the Order ID and Line Item ID fields and it's displaying everything now with no error. I put the fields back in and it's still working. Not sure why it's working now but wasn't before...
    – quanras
    Dec 3, 2013 at 19:29

1 Answer 1


I was running into the same problem too. You have to create the relationship "Commerce Line Item: Order ID" and display the field "Commerce Order: Order ID" to get around the mysql error.

You can get the view by importing the following code:

    $view = new view();
    $view->name = 'commerce_line_items';
    $view->description = '';
    $view->tag = 'default';
    $view->base_table = 'commerce_line_item';
    $view->human_name = 'Commerce Line Items';
    $view->core = 7;
    $view->api_version = '3.0';
    $view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

    /* Display: Master */
    $handler = $view->new_display('default', 'Master', 'default');
    $handler->display->display_options['title'] = 'Commerce Line Items';
    $handler->display->display_options['use_more_always'] = FALSE;
    $handler->display->display_options['access']['type'] = 'none';
    $handler->display->display_options['cache']['type'] = 'none';
    $handler->display->display_options['query']['type'] = 'views_query';
    $handler->display->display_options['exposed_form']['type'] = 'basic';
    $handler->display->display_options['pager']['type'] = 'full';
    $handler->display->display_options['pager']['options']['items_per_page'] = '10';
    $handler->display->display_options['style_plugin'] = 'default';
    $handler->display->display_options['row_plugin'] = 'fields';
    /* Relationship: Commerce Line Item: Order ID */
    $handler->display->display_options['relationships']['order_id']['id'] = 'order_id';
    $handler->display->display_options['relationships']['order_id']['table'] = 'commerce_line_item';
    $handler->display->display_options['relationships']['order_id']['field'] = 'order_id';
    /* Field: Commerce Line Item: Line item ID */
    $handler->display->display_options['fields']['line_item_id']['id'] = 'line_item_id';
    $handler->display->display_options['fields']['line_item_id']['table'] = 'commerce_line_item';
    $handler->display->display_options['fields']['line_item_id']['field'] = 'line_item_id';
    /* Field: Commerce Order: Order ID */
    $handler->display->display_options['fields']['order_id_1']['id'] = 'order_id_1';
    $handler->display->display_options['fields']['order_id_1']['table'] = 'commerce_order';
    $handler->display->display_options['fields']['order_id_1']['field'] = 'order_id';
    $handler->display->display_options['fields']['order_id_1']['relationship'] = 'order_id';
    $handler->display->display_options['fields']['order_id_1']['link_to_order'] = 'customer';
  • You also need to remove the field "Commerce Line Item: Order Id" if it's been added. Nov 3, 2015 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.