Unknown column errors because of table alias-Collection of common programming errors

Because of truncated identifiers with more than 63 chars, causing Views to break

It causes errors like

SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘taxonomy_term_data_node__draggableviews_structure_weight_coalesce’ in ‘order clause’

Goal: I want to change the table alias from _draggableviews_structure_ to _dv_

If I change

    $data['draggableviews_structure']['weight'] = array(
      'title' => t('Weight'),
      'group' => t('Draggableviews'),
      'field' => array(
        'help' => t('Display the weight value.'),
        'handler' => 'views_handler_field_numeric',
        'click sortable' => TRUE,
      ),
      'sort' => array(
        'help' => t('Sort entities by the draggableviews weight table field.'),
        'handler' => 'draggableviews_handler_sort',
      ),
    );

    $data['draggableviews_structure']['parent'] = array(
      'title' => t('Parent'),
      'help' => t('The parent entity id.'),
      'group' => t('Draggableviews'),
      'field' => array(
        'handler' => 'views_handler_field_numeric',
      ),
    );

  // Explain to every entity how to join with draggableviews structure table.
    $data['draggableviews_structure']['table']['join'][$info['base table']] = array(
      'handler' => 'draggableviews_join_handler',
      'left_table' => $info['base table'], // Because this is a direct link it could be left out.
      'left_field' => $info['entity keys']['id'],
      'field' => 'entity_id',
    );

to

   $data['dv']['weight'] = array(
     'title' => t('Weight'),
     'group' => t('Draggableviews'),
     'field' => array(
       'help' => t('Display the weight value.'),
       'handler' => 'views_handler_field_numeric',
       'click sortable' => TRUE,
     ),
     'sort' => array(
       'help' => t('Sort entities by the draggableviews weight table field.'),
       'handler' => 'draggableviews_handler_sort',
     ),
   );

   $data['dv']['parent'] = array(
     'title' => t('Parent'),
     'help' => t('The parent entity id.'),
     'group' => t('Draggableviews'),
     'field' => array(
       'handler' => 'views_handler_field_numeric',
     ),
   );

   // Explain to every entity how to join with draggableviews structure table.
   $data['dv']['table']['join'][$info['base table']] = array(
     'handler' => 'draggableviews_join_handler',
     'table' => 'draggableviews_structure',
     'left_table' => $info['base table'], // Because this is a direct link it could be left out.
     'left_field' => $info['entity keys']['id'],
     'field' => 'entity_id',
   );

This works. However, all of the views with draggableviews fields/sort components have disappeared. In the admin view, views complains able missing relationship, and it trying to fix them for you. If you read the fields, the view works again.

Is there a way to fix this, without manually changing everything? Is there a better way to solve it?

1. I’ve used the MD5 alias patch from that issue queue here: http://drupal.org/files/issues/views-571548_40.patch (#40) to fix that issue.

   You may need to apply the patch manually to views/includes/query.inc

   The good news is, if you can do that, it should fix all those alias truncation issues for you.

Originally posted 2013-11-09 23:19:38.