Complex views no longer work

For something like a SUBSTRING call, you want to be using ->where(), not ->condition(). That just wasn't properly enforced until the patch mentioned in #6 went in, when it became necessary for security to do so.

->where("SUBSTRING(node.title, :start, :length)", array(':start' => 1, ':length' => 1)) should work. (Or something like that.) Remember the placeholders for proper injection safety.

Also, don't worry about the excessive parentheses. The DB layer is very liberal with parenthesis so as to make sure it doesn't miss any and introduce subtle bugs. It shouldn't hurt anything.

I've run into this now trying to do complex queries for Date and Calendar, would love to find a solution. The approach in #4 looks logical to me, I don't really understand #5. And I already saw in the dbtng documentation that we need ->where() instead of ->condition(), so I'm glad to see someone working on this solution.

The patch above worked for me, but I tried to incorporate Crell's comments by making a simple change, which did not work. I just swapped in one more placeholder and added the placeholder to the args() array. This certainly looks like it should work but it blows up completely.

  function get_formula() {
    return "SUBSTRING($this->table_alias.$this->real_field, 1, :length)";
  }

    if ($formula) {
      $field .= ' = :views_handler_argument_string';
      $argument = array(
        ':length' => intval($this->options['limit']),
        ':views_handler_argument_string' => $argument,
      );
      $this->query->add_where(0, $field, $argument, 'formula');
    }
    else {
      $this->query->add_where(0, $field, $argument);
    }

After some analysis I think I see the problem. That formula is used in the WHERE clause but it is also used as a field, and my 'fix' changed a field with no placeholders into a field with placeholders. The current Views code does not know anything about placeholders in fields. The compile_fields() function checks for fields that are formulas and uses addExpression() on them, which is right, but there is no provision for passing placeholders to that function, which is also going to be necessary to support complex queries. I can tell you for sure that Date module has fields which are formulas and I'm sure there are others.

So we need to stretch this patch to provide a way to supply placeholders for formula fields that need them as well as take care of the formula in the WHERE clause.

Subscribing for now. Obviously, very interested in helping however I can from the Drupal core side to make Views work in D7. :)

Changing the title and marking this critical, since even some built-in views that used to work are now broken

So here's a stab at fixing both problems by using the $params in add_field to add a param for a 'placeholders' array. Then we just add that to ->addExpression(). This patch doesn't work, I get an error 'Unsupported operand types in ...views/plugins/views_plugin_query_default.inc on line 667' that I can't figure out, but I think the approach could work if someone can see what I did wrong.

Somehow I reverted the status when I added the patch.

Yay! Yes, glossary is fixed with that. Now going to see if I can use this change to fix archive and date views.

Here's a patch updated with a fix for the archive view. I haven't tried this on the Date module yet, but I think this is looking like a workable system.

Actually made one small change, just for clarity in reading the code. When we change the argument to an array of placeholders, I changed the name of the variable to $placeholders rather than $argument.

Great! I have a working glossary and archive view with this patch. Can't see any errors or anything else that might have been broken along the way. Archive works with both page and block view.

I was going to be sure it would also work for Date but hopefully if I find other changes are needed you will help me get them committed as follow-on patches. This much at least gets some of the important core views working again and provides a method for other modules to do some more complex queries.

So I would say go ahead and commit it :)

And it also gets rid of the error messages in the Date filter and arguments. They still have other things broken, but getting this much working right is huge. Now I can pass my complex date queries to Views and Views can handle them in a meaningful way.

YAY!! Thank you so much, all! :D

I just noticed that comments_recent still seems to be "broken". I'm no good at writing patches for PHP code so here's just a report :)

I guess this needs to be "needs work" then.

Did some more testing. Tracker view is fine, but taxonomy_term is not and I also cannot add a rather simple list of taxonomy terms without the PDOException: SQLSTATE[42S02]: Base table or view not found error message. "backlinks" View as well as "frontpage" seem to work just fine as well.

I suspect we haven't uncovered a complete list of all the affected places. We need to start identifying them and then we can fix them. The core fix is still good and at least *some* of the views are working now.

Okay, marking this as fixed. The only other thing I found concerned taxonomy and there's already an issue for it. Thanks guys.