Hello:

I just finished performing an upgrade from Views 7.x-3.7 to Views 7.x-3.8.

I created a block view of users using representative nodes.

After the upgrade, the second block no longer appears.

Here is the error message that's showing up with the second block:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.uid1' in 'where clause'

The view works fine in Views 7.x-3.7.

The exported view code is identical in both 3.7 and 3.8.

I don't understand why it stopped working.

The exported view is attached.

Thanks for any help you can give me.

UPDATE (July 1, 2014) - We seem to have tracked the problem to a file in the views/handlers dir:

views_handler_relationship_groupwise_max.inc

Here's the differing section of code in the old file:

    // Replace subquery argument placeholders.
    $quoted = $subquery->getArguments();
    $connection = Database::getConnection();
    foreach ($quoted as $key => $val) {
      if (is_array($val)) {
        $quoted[$key] = implode(', ', array_map(array($connection, 'quote'), $val));
      }
      // If the correlated placeholder has been located, replace it with the outer field name.
      elseif ($val === '**CORRELATED**') {
       $quoted[$key] = $this->definition['outer field'];
      }
      else {
        $quoted[$key] = $connection->quote($val);
      }
    }
    $subquery_sql = strtr($subquery_sql, $quoted);

and the new file:

    // Replace the placeholder with the outer, correlated field.
    // Eg, change the placeholder ':users_uid' into the outer field 'users.uid'.
    // We have to work directly with the SQL, because putting a name of a field
    // into a SelectQuery that it does not recognize (because it's outer) just
    // makes it treat it as a string.
    $outer_placeholder = ':' . str_replace('.', '_', $this->definition['outer field']);
    $subquery_sql = str_replace($outer_placeholder, $this->definition['outer field'], $subquery_sql);

Jordan

Here's an export of the view:

$view = new view();
$view->name = 'accordion';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'users';
$view->human_name = 'Accordion';
$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'] = 'Accordion';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['access']['perm'] = 'access user profiles';
$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'] = 'some';
$handler->display->display_options['pager']['options']['items_per_page'] = '0';
$handler->display->display_options['pager']['options']['offset'] = '0';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Relationship: User: Representative node */
$handler->display->display_options['relationships']['uid_representative']['id'] = 'uid_representative';
$handler->display->display_options['relationships']['uid_representative']['table'] = 'users';
$handler->display->display_options['relationships']['uid_representative']['field'] = 'uid_representative';
$handler->display->display_options['relationships']['uid_representative']['subquery_sort'] = 'node.created';
$handler->display->display_options['relationships']['uid_representative']['subquery_view'] = '';
$handler->display->display_options['relationships']['uid_representative']['subquery_namespace'] = '';
/* Field: User: User image */
$handler->display->display_options['fields']['field_user_image']['id'] = 'field_user_image';
$handler->display->display_options['fields']['field_user_image']['table'] = 'field_data_field_user_image';
$handler->display->display_options['fields']['field_user_image']['field'] = 'field_user_image';
$handler->display->display_options['fields']['field_user_image']['label'] = '';
$handler->display->display_options['fields']['field_user_image']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['field_user_image']['click_sort_column'] = 'fid';
$handler->display->display_options['fields']['field_user_image']['settings'] = array(
  'image_style' => 'user_thumb',
  'image_link' => '',
);
/* Field: User: Name */
$handler->display->display_options['fields']['name']['id'] = 'name';
$handler->display->display_options['fields']['name']['table'] = 'users';
$handler->display->display_options['fields']['name']['field'] = 'name';
$handler->display->display_options['fields']['name']['label'] = '';
$handler->display->display_options['fields']['name']['alter']['make_link'] = TRUE;
$handler->display->display_options['fields']['name']['alter']['path'] = 'stories/[name]';
$handler->display->display_options['fields']['name']['alter']['replace_spaces'] = TRUE;
$handler->display->display_options['fields']['name']['alter']['path_case'] = 'lower';
$handler->display->display_options['fields']['name']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['name']['link_to_user'] = FALSE;
/* Field: Content: Title */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
$handler->display->display_options['fields']['title']['relationship'] = 'uid_representative';
$handler->display->display_options['fields']['title']['label'] = '';
$handler->display->display_options['fields']['title']['element_label_colon'] = FALSE;
/* Sort criterion: User: Name */
$handler->display->display_options['sorts']['name']['id'] = 'name';
$handler->display->display_options['sorts']['name']['table'] = 'users';
$handler->display->display_options['sorts']['name']['field'] = 'name';
/* Filter criterion: User: Active */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'users';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = '1';
$handler->display->display_options['filters']['status']['group'] = 1;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: Content: Published */
$handler->display->display_options['filters']['status_1']['id'] = 'status_1';
$handler->display->display_options['filters']['status_1']['table'] = 'node';
$handler->display->display_options['filters']['status_1']['field'] = 'status';
$handler->display->display_options['filters']['status_1']['relationship'] = 'uid_representative';
$handler->display->display_options['filters']['status_1']['value'] = '1';
/* Filter criterion: Content: Author uid */
$handler->display->display_options['filters']['uid']['id'] = 'uid';
$handler->display->display_options['filters']['uid']['table'] = 'node';
$handler->display->display_options['filters']['uid']['field'] = 'uid';
$handler->display->display_options['filters']['uid']['relationship'] = 'uid_representative';

/* Display: Columnists */
$handler = $view->new_display('block', 'Columnists', 'block_1');
$handler->display->display_options['defaults']['title'] = FALSE;
$handler->display->display_options['title'] = 'Columnists';
$handler->display->display_options['defaults']['filter_groups'] = FALSE;
$handler->display->display_options['defaults']['filters'] = FALSE;
/* Filter criterion: User: Active */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'users';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = '1';
$handler->display->display_options['filters']['status']['group'] = 1;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: User: Name */
$handler->display->display_options['filters']['uid']['id'] = 'uid';
$handler->display->display_options['filters']['uid']['table'] = 'users';
$handler->display->display_options['filters']['uid']['field'] = 'uid';
$handler->display->display_options['filters']['uid']['value'] = array(
  0 => '222495',
  1 => '222507',
  2 => '222494',
  3 => '222498',
  4 => '222496',
  5 => '222497',
);
$handler->display->display_options['filters']['uid']['group'] = 1;
/* Filter criterion: Content: Published */
$handler->display->display_options['filters']['status_1']['id'] = 'status_1';
$handler->display->display_options['filters']['status_1']['table'] = 'node';
$handler->display->display_options['filters']['status_1']['field'] = 'status';
$handler->display->display_options['filters']['status_1']['relationship'] = 'uid_representative';
$handler->display->display_options['filters']['status_1']['value'] = '1';
$handler->display->display_options['filters']['status_1']['group'] = 1;

/* Display: Writers */
$handler = $view->new_display('block', 'Writers', 'block_2');
$handler->display->display_options['defaults']['title'] = FALSE;
$handler->display->display_options['title'] = 'Writers';
$handler->display->display_options['defaults']['sorts'] = FALSE;
/* Sort criterion: User: Name */
$handler->display->display_options['sorts']['name']['id'] = 'name';
$handler->display->display_options['sorts']['name']['table'] = 'users';
$handler->display->display_options['sorts']['name']['field'] = 'name';
$handler->display->display_options['sorts']['name']['order'] = 'DESC';
$handler->display->display_options['defaults']['filter_groups'] = FALSE;
$handler->display->display_options['defaults']['filters'] = FALSE;
/* Filter criterion: User: Active */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'users';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = '1';
$handler->display->display_options['filters']['status']['group'] = 1;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: User: Name */
$handler->display->display_options['filters']['uid']['id'] = 'uid';
$handler->display->display_options['filters']['uid']['table'] = 'users';
$handler->display->display_options['filters']['uid']['field'] = 'uid';
$handler->display->display_options['filters']['uid']['value'] = array(
  0 => '123317',
  1 => '123323',
  2 => '123324',
  3 => '222483',
  4 => '222484',
  5 => '222485',
  6 => '222489',
  7 => '222490',
);
/* Filter criterion: Content: Published */
$handler->display->display_options['filters']['status_1']['id'] = 'status_1';
$handler->display->display_options['filters']['status_1']['table'] = 'node';
$handler->display->display_options['filters']['status_1']['field'] = 'status';
$handler->display->display_options['filters']['status_1']['relationship'] = 'uid_representative';
$handler->display->display_options['filters']['status_1']['value'] = '1';
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

bardenjed’s picture

Issue summary: View changes
nvahalik’s picture

Version: 7.x-3.8 » 7.x-3.x-dev
Status: Active » Needs review
FileSize
1.53 KB

If your View's base table is the table defined in the outer field, then this will operate as expected. The problem comes when you're using a relationship to add this field. The table never gets adjusted to the name of the related table.

Attached is a patch that ensures the proper table is substituted when a relationship is present. It does so by performing a simple string substitution on the outer field by replacing the argument table present in the field with $this->relationship.

What I don't fully understand is the purpose of outer field since argument table and argument field contain enough information for you build the query.

fonant’s picture

Patch in #2 fixes a "representative payment transaction" relationship for a Drupal Commerce line_items view.

bmango’s picture

Can confirm that the patch in #2 fixed this issue for me when using a representative node relationship on a user view.

Many thanks!

bmango’s picture

Please note that if the patch for the issue Taxonomy term "Representative node" views with filters and sorts don't work is committed first then the code for this patch will need to be updated as both this patch and the one for the other issue update the same file. I think the code for this patch will be straight forward to change though.

fonant’s picture

Just a quick note that the patch in #2 is still needed, and still works, in Views 7.x-3.14.

bmango’s picture

Status: Needs review » Reviewed & tested by the community

Setting to RTBC.

fonant’s picture

Patch fails to apply for Views 7.x-3.15

EDIT: no, this patch does apply OK, but it conflicts with views-representative_view-1417090-82.patch from #1417090: Taxonomy term "Representative node" views with filters and sorts don't work which may or may not also be needed.

Here's a modified patch file that seems to work if you have already applied the patch from #1417090: Taxonomy term "Representative node" views with filters and sorts don't work:

diff --git a/handlers/views_handler_relationship_groupwise_max.inc b/handlers/views_handler_relationship_groupwise_max.inc
index 6f08dfa..9771c8d 100644
--- a/handlers/views_handler_relationship_groupwise_max.inc
+++ b/handlers/views_handler_relationship_groupwise_max.inc
@@ -297,7 +297,12 @@ class views_handler_relationship_groupwise_max extends views_handler_relationshi
       // If the correlated placeholder has been located, replace it with the
       // outer field name.
       elseif ($val === '**CORRELATED**') {
-        $quoted[$key] = $this->definition['outer field'];
+        if ($this->relationship) {
+          $quoted[$key] = str_replace($this->definition['argument table'], $this->relationship, $this->definition['outer field']);
+        }
+        else {
+          $quoted[$key] = $this->definition['outer field'];
+        }
       }
       else {
         $quoted[$key] = $connection->quote($val);
DamienMcKenna’s picture

Lets have testbot try it out.

fonant’s picture

Confirming that views-n2295379-9.patch works here :)

fonant’s picture

Confirming that this patch is still needed, and still works, after the latest Views update.

joegl’s picture

Confirming patch works. Specifically fixed a commerce_order.order_id 'where' clause SQLSTATE error in some custom commerce views. Not sure if related to payment or not.

DamienMcKenna’s picture

DamienMcKenna’s picture

A minor improvement on the huge comment.

DamienMcKenna’s picture

Status: Reviewed & tested by the community » Fixed
Issue tags: -#views

Committed, thanks everyone!

Status: Fixed » Closed (fixed)

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

joegl’s picture

Is this really fixed and committed? I was running views 3.14 with the patch and had it fixed. Then we upgraded to 3.20 and now the error is back. It also looks like the pre and post-patch code is entirely different than what is in the handlers/views_handler_relationship_groupwise_max.inc code right now.

This is the patch code for the subquery:

// Extract the SQL the temporary view built.
    $subquery_sql = $subquery->__toString();
+    // Replace the placeholder with the outer, correlated field. For example,
+    // change the placeholder ':users_uid' into the outer field 'users.uid'. It
+    // is necessary to work directly with the SQL, because putting a name of a
+    // field into a SelectQuery that it does not recognize (because it's outer)
+    // just makes it treat it as a string. Using this handler on a view with a
+    // base table that isn't the one defined in the outer field causes SQL
+    // errors since the base table is hard-coded in the handler data. So
+    // replace the outer field base table with the relationship. We use the
+    // argument table since it will always match the outer field's table.
+    // @see https://www.drupal.org/node/2295379
+    if ($this->relationship) {
+      $outer_field = str_replace($this->definition['argument table'], $this->relationship, $this->definition['outer field']);
+    }
+    else {
+      // Just use the default outer field.
+      $outer_field = $this->definition['outer field'];
+    }
     $outer_placeholder = ':' . str_replace('.', '_', $this->definition['outer field']);
+    $subquery_sql = str_replace($outer_placeholder, $outer_field, $subquery_sql);

return $subquery_sql;

This is what is actually in the file now (3.20):

    // Extract the SQL the temporary view built.
    $subquery_sql = $subquery->__toString();

    // Replace subquery argument placeholders.
    $quoted = $subquery->getArguments();
    $connection = Database::getConnection();
    foreach ($quoted as $key => $val) {
      if (is_array($val)) {
        $quoted[$key] = implode(', ', array_map(array($connection, 'quote'), $val));
      }
      // If the correlated placeholder has been located, replace it with the
      // outer field name.
      elseif ($val === '**CORRELATED**') {
        $quoted[$key] = $this->definition['outer field'];
      }
      else {
        $quoted[$key] = $connection->quote($val);
      }
    }
    $subquery_sql = strtr($subquery_sql, $quoted);

    return $subquery_sql;

Whatever happened here, the issue is back in 3.20. And I am not sure what to do, because the patch no longer applies and the entire codeblock was changed.

joegl’s picture

In the debug SQL preview I'm seeing a lot of random "INNER"'s sprinkled throughout parts of the query:

LEFT JOIN {commerce_payment_transaction} commerce_payment_transaction_commerce_order ON (SELECT commerce_payment_transactionINNER.transaction_id AS transaction_idINNER
FROM 
{commerce_payment_transaction} commerce_payment_transactionINNER
LEFT JOIN {commerce_order} commerce_orderINNER ON commerce_payment_transactionINNER.order_id = commerce_orderINNER.order_id
WHERE (( (commerce_orderINNER.order_id = commerce_order.order_id ) ))
ORDER BY commerce_payment_transactionINNER.transaction_id DESC

This doesn't look right to me. It appears as though it's using INNER as an alias but INNER was not defined before this line (this is the first part of the query where "INNER" appears). Or my reading comprehension is off.

EDIT: The specific error I'm receiving is: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'commerce_order.order_id' in 'where clause'. I am using Drupal 7 on the latest security releases with Views 3.20 and Commerce 3.14. We have verified a similar view working correctly on the same exact platforms on a separate site -- I am going to spend some more time looking into whether or not this is a Views issue or site-specific, but as it was the exact same error we received pre-patch a month ago, I presumed it was relevant.

joegl’s picture

I narrowed this down to the "Representative Payment Transaction" relationship provided by Commerce. I am not sure if the problem is with the Views module, or the Commerce Views Integration for this specific relationship.

I have reproduced the issue on two separate production sites, including the site I listed as "working" above (once I added the Payment Method relationship it spat out the same error).

joegl’s picture

joegl’s picture

This is definitely a views issue, specfically for "Representative Nodes". I attempted to use both the Taxonomy and User Representative Node relationships that are included in the Views module itself, and got the same exact SQLSTATE error.

It appears this issue is not actually fixed and the views_handler_relationship_groupwise_max code is still broken, despite seemingly to have newly written code. I am going to close the commerce issue and I believe this one should be re-opened, but I cannot do it. I am tempted to create another issue relate it here, since I can't re-open.

joegl’s picture

I'm not sure in the Views code where the problem is, but the issue is how the SQL query is constructed. In my view their is a relationship to the user based on the owner of the order (uid column on commerce_order table). In the MySQL query, the relationship is created via a LEFT JOIN on the users table, but it is aliased like so:

LEFT JOIN users users_commerce_order ON commerce_order.uid = users_commerce_order.uid

Also in the view is the User Representative Node relationship based on the Order Owner. This relationship creates the query error. I noticed the where clause containing the error doesn't use the alias for the users table (users_commerce_order) but instead tries to call the table directly:

WHERE (( (users_nodeINNER.uid = users.uid ) ))

I copied the query, and changed the above line to:
WHERE (( (users_nodeINNER.uid = users_commerce_order.uid ) ))

And the query worked as expected. I'm still trying to figure out how to modify the code to get the correct WHERE condition and table alias into the query.

EDIT: This issue also only seems to be in play if the relationship is based off another relationship. For example, if I create a view of users, and create a representative relationship, it works. If I create a view of nodes, create a relationship from the node to the user (author), and then use the author relationship to create a representative relationship, it doesn't work. So it appears that when you create a relationship, it aliases the JOIN, but when you try to build off that relationship with a representative node, it fails to recall the alias from the parent relationship/JOIN.

joegl’s picture

I feel like I'm getting close. The correct table alias sits at on the table_alias property ($this->table_alias). We just need to make sure the base table is substituted with the alias at some point. Still figuring out how/where to do this.

joegl’s picture

Version: 7.x-3.x-dev » 7.x-3.20

I have to wrap up for the day but I was able to resolve the issue by incorporating a few lines of code from the patch in this issue to the 3.20 codebase:

      // If the correlated placeholder has been located, replace it with the
      // outer field name.
      elseif ($val === '**CORRELATED**') {
        // if there is a relationship replace the outer field with the correct
        // table alias
        $outer_field = $this->definition['outer field'];
        if($this->relationship) {
          $outer_field = str_replace($this->definition['argument table'], $this->relationship, $this->definition['outer field']);
        }
        $quoted[$key] = $outer_field;
      }

I'm not entirely sure what the other two conditions do for the $quoted as $key => $val loop (looping through the arguments). All I know is incorporating the above rewrite of the 'outer field' into the $val === '**CORRELATED**' condition worked, as it replace the table specified in the 'outer field' with the table_alias for the relationship (argument table).

I will see if I can't push this into a patch when I have some more time, but I would also like to have someone who knows the loop conditions better than I to review and correct it!

joegl’s picture

I had a little extra time. Pushed it into a patch.

joegl’s picture

Version: 7.x-3.20 » 7.x-3.x-dev

Moving version back to Dev, since that's what I patched, but should apply to 3.20 as well.

bmango’s picture

Patch in #26 works as expected. Many thanks!

g33kg1rl’s picture

Patch in #26 fixed the issue for me! Thank you joegl!