Unknown column caused by orderby alias not matching field alias

2 #1 or even from {draggableviews_structure} to {dw}

I do not understand why its getting cut off. 66 is a random. Common database numbers are L=2^X-1, like 31,63,127,255.

This sounds more like a Views problem than a draggableviews issue

I do not like the #1 patch. It's a fix for your query, as it is shorter, but it might not be to someone else.

If there is a hard limit to the length in the view then:

1. Increase the length
2. Trim to the max length and check if the trim version is being used

IMHO module tables should have prefix like views_drag_ .

This naming placed them near main views tables and clearly distinct they roots and purpose.

Regarding the latest version 2.0 of the draggableviews module issues with aliases are solved by following:

$as = $this->table_alias . '_' . $this->real_field;
$as = $this->query->add_field($this->table_alias, $this->real_field, $as, array());
$this->query->orderby[] = array(
  'field' => 'COALESCE(' . $as . ', 10000)',
   'direction' => drupal_strtoupper($this->options['order'])
);

Nothing to fix further.

Ok this patch changes all the tables alias from 'draggableviews_structure' to 'dv'. The only problem is that it break every view using draggableviews.

I believe a draggableviews_update_72XX() hook can solve this, where it does a regular expression change from 'draggableviews_structure' to 'dv' for all the views.

The other option that springs to my mind is what was done in 7.x-1.x. In the previous version it did a check and replacement if the relationships were broken.

Thank you @andypost. I never though about features.

Here at University of Waterloo we use features extensively. Correct me if I am wrong...On the local development, if we update the views in the hook_update(), then all of the features will show as overridden (as all the code is in the database). They will need to be re-rolled. If you revert them, then there is no way to fix them, unless you do them manually (as you cannot re-run an update_hook).

On Staging/Live, without the new features pushed to it, when you run the update_hook, then all of the views will break, as none of the views are saved to the database.

If you push the features to Staging/Live, the views will break.
If you push the features to Staging/Live and update your draggableviews from 7.x-2.0 to a version where #8 is committed, then everything will work
If you push the features to Staging/Live and update your draggableivews from 7.x-2.0 to a version where #8 is committed, and run update.php, everything works.

So the only way not to break features is to add a miss relationship catch like what was kinda done in 7.x-1.x-dev.

Other suggestion is to roll this out in 7.x-3.0 and say screw everyone with features.

Ok I believe I actually found the problem.

So Views truncates the field to 60 characters in length. We do not truncate the orderby field, therefore they do not match.

Now if we call add_orderby() function instead of add_field() & set the orderby ourselfs (see attached patch), views will add the field and validate the alias for us.

From views/plugins/views_plugin_query_default.inc

  /**
   * Add an ORDER BY clause to the query.
   *
   * @param $table
   *   The table this field is part of. If a formula, enter NULL.
   *   If you want to orderby random use "rand" as table and nothing else.
   * @param $field
   *   The field or formula to sort on. If already a field, enter NULL
   *   and put in the alias.
   * @param $order
   *   Either ASC or DESC.
   * @param $alias
   *   The alias to add the field as. In SQL, all fields in the order by
   *   must also be in the SELECT portion. If an $alias isn't specified
   *   one will be generated for from the $field; however, if the
   *   $field is a formula, this alias will likely fail.
   * @param $params
   *   Any params that should be passed through to the add_field.
   */
  function add_orderby($table, $field = NULL, $order = 'ASC', $alias = '', $params = array()) {
    // Only ensure the table if it's not the special random key.
    // @todo: Maybe it would make sense to just add a add_orderby_rand or something similar.
    if ($table && $table != 'rand') {
      $this->ensure_table($table);
    }

    // Only fill out this aliasing if there is a table;
    // otherwise we assume it is a formula.
    if (!$alias && $table) {
      $as = $table . '_' . $field;
    }
    else {
      $as = $alias;
    }

    if ($field) {
      $as = $this->add_field($table, $field, $as, $params);
    }

    $this->orderby[] = array(
      'field' => $as,
      'direction' => strtoupper($order)
    );

  /**
   * Add a field to the query table, possibly with an alias. This will
   * automatically call ensure_table to make sure the required table
   * exists, *unless* $table is unset.
   *
   * @param $table
   *   The table this field is attached to. If NULL, it is assumed this will
   *   be a formula; otherwise, ensure_table is used to make sure the
   *   table exists.
   * @param $field
   *   The name of the field to add. This may be a real field or a formula.
   * @param $alias
   *   The alias to create. If not specified, the alias will be $table_$field
   *   unless $table is NULL. When adding formulae, it is recommended that an
   *   alias be used.
   * @param $params
   *   An array of parameters additional to the field that will control items
   *   such as aggregation functions and DISTINCT.
   *
   * @return $name
   *   The name that this field can be referred to as. Usually this is the alias.
   */
  function add_field($table, $field, $alias = '', $params = array()) {
    // We check for this specifically because it gets a special alias.
    if ($table == $this->base_table && $field == $this->base_field && empty($alias)) {
      $alias = $this->base_field;
    }

    if ($table && empty($this->table_queue[$table])) {
      $this->ensure_table($table);
    }

    if (!$alias && $table) {
      $alias = $table . '_' . $field;
    }

    // Make sure an alias is assigned
    $alias = $alias ? $alias : $field;

    // PostgreSQL truncates aliases to 63 characters: http://drupal.org/node/571548

    // We limit the length of the original alias up to 60 characters
    // to get a unique alias later if its have duplicates
    $alias = strtolower(substr($alias, 0, 60));

    // Create a field info array.
    $field_info = array(
      'field' => $field,
      'table' => $table,
      'alias' => $alias,
    ) + $params;

    // Test to see if the field is actually the same or not. Due to
    // differing parameters changing the aggregation function, we need
    // to do some automatic alias collision detection:
    $base = $alias;
    $counter = 0;
    while (!empty($this->fields[$alias]) && $this->fields[$alias] != $field_info) {
      $field_info['alias'] = $alias = $base . '_' . ++$counter;
    }

    if (empty($this->fields[$alias])) {
      $this->fields[$alias] = $field_info;
    }

    // Keep track of all aliases used.
    $this->field_aliases[$table][$field] = $alias;

    return $alias;
  }

Patch Attached. This does not break features. Ignore #8 Patch.

Committed e7e2d4e

I believe this does not need to be backported to 6.x as Coalese was added to 7.x-2.x. Reopen if I am wrong.

Patch #11 also add the orderby order if 'New items at bottom' is unchecked. Now for the sort order display, you cannot change this (as it is removed), however for the page display you can not change this. It still defaults to ASC.

[repeat of #12]