support subqueries

@fago - I'd love to write something more elegant that supports sub-queries using a pretty API. But my implementation here was the minimum amount necessary to get it working.

I agree that this is a bit of a hack. There's a problem with the subquery arguments that will only be solved by rewriting with a nicer query object interface. The problem is that if the subquery uses substitutable arguments, the only way to get these into the query is with add_where(), and if you do that, the ordering of the query arguments gets sketchy.

My implementation here was the minimum amount necessary to get it working. If more code that implements a nicer query interface would actually be considered, then I'll do that.

@moshe, I don't know the official meaning of the status's and if one state over another makes it such that more people look at it. Given what fago said, and what I just commented on, changing this back to "needs work" is fine with me.

As for the comment that Drupal and views does not support subqueries..., This would not actually make views dependent on subqueries, but allow views to support them. With the upcoming 6.x mysql requirements at 4.1 or higher, getting ready to support subqueries is, IMHO, a good thing.

Agreed, yesterday I started to try and implement this yesterday and ran into the wall that this is going to impact quite a bit, especially in query(). But it appears that we're on the same path, which is encouraging I thought I posted this, but evidentially I didn't. Here's the initial function:

function add_subquery($subquery, $joininfo) {
  if ($joininfo) {
    $this->joins['subquery:'. $subquery->query()][] = $joininfo;
  }
  $this->tablequeue[] = array('query' => $query, 'alias_prefix' => $this->use_alias_prefix);
}

Do you have any suggestions.

Attached is a second pass at this implementation. This used the add_subquery method call to clearly distinguish that we're adding a query and not a table. As such, it also takes some shortcuts in the table definition and joins, since we shouldn't have more than one inclusion of each subquery. I'm currently using this on views_fastsearch 5.x-dev version with a conditional check using method_exists($query, 'add_subquery')

Attached patch adds a function comment.

Hrm, subqueries are going to cause problems with db_rewrite_sql because db_rewrite_sql does a str_replace on the WHERE, GROUP, or ORDER words, within knowledge of where they occur in the SQL. I'll create a new issue and attach the issue number here.

See 151910 for db_rewrite_sql issue.

@Moshe, I think that you are you proposing that _views_query::query() inject a placeholder in the query for the sub-queries and another array element for the actual sub-queries, that _views_build_query and _views_get_query return and that the sub-query be injected on that views.module+538.

That's not an aweful solution, but it feels a bit like a hack. Do you have a reason for wanting to solve this in views rather than in core, other than the fact that it will be easier to get done in views. I will try to code this up for views tomorrow, but I'd still like to work on a cleaner solution for 6.x core.

I've offered a solution for core (#151910), but as this will at best, not be available until 6.x, I've also taken Moshe's suggestion and re-rolled this patch with such that the sub-query is not passed to db_rewrite_sql.

I was thinking that views sub-queries would be used by contrib modules that wanted them and that they'd come with a database/version requirements.

I think that pgsql supports sub-queries as of 7.1 and mysql supports sub-queries as of 4.1. The current Drupal requirements for pgsql of 7.3 already supports sub-queries and the coming 6.x requirements for mysql will support sub-queries. However, if we wanted to be compatible with all databases, I could add a check for mysql prior to 4.1 and fallback to using db_temporary_query() in that case.

Essentially I've done this in views_fastsearch, when this patch isn't available.

  if (method_exists($query, 'add_subquery')) {
    $query->add_subquery($sql, $terms, $join, 'temp_vfs');
    $query->add_field('score', 'temp_vfs');
  }
  elseif (db_query_temporary($sql, $terms, 'temp_vfs')) {
    $query->add_table('temp_vfs', FALSE, 1, $join);
    $query->add_field('score', 'temp_vfs');
  }

$GLOBALS['db_type'] returns the database engine (mysql/pgsql). Does Drupal have a handy variable (that I'm not aware of) to detect the mysql version, or do we need save something? If not, it makes sense to do this once on install or update, and not do it again until the next update. Where would this check go? Once the .install update hook runs, it won't get run again. So where do we put something that should run on every update.php?

mysql> select version();
mysql> show variables like 'version';

The attached patch adds a function _views_is_subquery() so authors can check for sub-query support, and it uses the function in add_subquery to fallback to db_temporary_query if needed. I like the graceful fallback, but I'm not sure if this is more than you were asking for.

I'm not sure if views changed, or if I never tested arguments. But here's an updated patch that fixes subquery arguments that get cached.

Marking back to CNR.

I ran into some problems involving subquery arguments and caching. If the subquery has arguments and the query has arguments, the original patch tracked them and let pager_query handle them. But a subsequent change to views did the query substitution before caching, scrambling the subquery arguments.

IMO we just can't views cache subqueries. This is because we can't db_rewrite_sql subqueries. (The core handler simply isn't smart enough to handle the string.) Since we can't db_rewrite_sql subqueries, my solution is to use the [SUBQUERY] replacement string, and with this solution the arguments can't be replaced at cache time.

This new patch just skips caching when views subqueries are used.

And here's one more fix, we need the query_substitution args...

I try not to RTBC my own patches, and I'm especially cautious with views. But I haven't created a views_fastsearch release for 6 months, because the next generation of VFS relies on this patch. I use this patch with VFS on all of my sites. I last updated the patch in October, and I just confirmed that the patch still applies. Please, could this be committed?

I use it all the time, when I write my custom views fields and sorts. The introduction to the issue has one such use.

Here's an example for retrieving the number of people who have answered "i love this", for use with the ilovethis.module:

    $sql = "SELECT nid, COUNT(*) AS num FROM {ilovethis} GROUP BY nid";
    $joininfo = array(
      'type' => 'outer',
      'left' => array('table' => 'node', 'field' => 'nid'),
      'right' => array('field' => 'nid'),
    );
    $query->add_subquery($sql, array(), $joininfo, 'ilovethis_supporters');
    $query->add_field('num', 'ilovethis_supporters', 'num_supporters');

Here's a similiar example from ilovethis, to rank the support:

    $sql = "SELECT nid, COUNT(*) AS num, (@rank=COALESCE(@rank, 1)) as rank FROM {ilovethis} GROUP BY nid";
    $joininfo = array(
      'type' => 'outer',
      'left' => array('table' => 'node', 'field' => 'nid'),
      'right' => array('field' => 'nid'),
    );
    $query->add_subquery($sql, array(), $joininfo, 'ilovethis_supporters_rank');
    $query->add_field('1/ilovethis_supporters_rank.rank', '', 'popularity_support_ranking');

Here's an example for og, to get the number of related documents:

  $sql = "SELECT COUNT(*) AS num, group_nid FROM {og_ancestry} GROUP BY group_nid";
  $joininfo = array(
    'type' => 'outer',
    'left' => array('table' => 'node', 'field' => 'nid'),
    'right' => array('field' => 'group_nid'),
  );
  $query->add_subquery($sql, array(), $joininfo, 'og_count');
  $query->add_field('num', 'og_count', 'og_count_num');

Here's another og example I use in a custom application to return the number of new related documents,

  $sql = "SELECT COUNT(*) AS num, a.group_nid FROM {node} n INNER JOIN {og_ancestry} a ON a.nid = n.nid LEFT JOIN {history} h ON h.nid = n.nid WHERE h.uid = %d AND n.changed > h.timestamp GROUP BY a.group_nid";
  $joininfo = array(
    'type' => 'outer',
    'left' => array('table' => 'node', 'field' => 'nid'),
    'right' => array('field' => 'group_nid'),
  );
  global $user;
  $query->add_subquery($sql, array($user->uid), $joininfo, 'og_new');
  $query->add_field('num', 'og_new', 'og_new_num');

Here's an example that sorts the og node based on whether the og documents are in a particular nodequeue:

    $sql = "SELECT a.group_nid, GREATEST(an.created, an.changed, an.comment) AS updated FROM {node} an INNER JOIN {og_ancestry} a ON a.nid = an.nid INNER JOIN {nodequeue_nodes} q ON q.nid = an.nid AND q.qid = %d";
    $joininfo = array(
      'type' => 'outer',
      'left' => array('table' => 'node', 'field' => 'nid'),
      'right' => array('field' => 'group_nid'),
    );
    $query->add_subquery($sql, array($tier1_qid), $joininfo, 'tier1');
    $query->orderby[] = 'tier1.updated '. $sort['sortorder'];

It's hard to do these types of queries without subqueries. You can do them, but it requires temporary tables and more code.

I'm not sure if the previous poster intentionally changed this from RTBC to Active, so I'm switching it back.

I tried to apply this recently to the latest version of views, and it no longer applies.

Scott, thanks for the re-rolls. I haven't looked at your changes close enough to comment, except:

• It included an additional change in views_ui.module, which I removed
• It included an additional change in one of the translation files which I removed

I discovered today, that the order of where the SUBQUERY occurs, and on my most recent project, I need the SUBQUERY last instead of first. I'm uploading a new patch, but I haven't tested it with views fastsearch. If this breaks VFS, someone please comment here, and I'll find another solution for this. Thanks!

@peashooter, The latest patch works on at least a half dozen of my projects, including the one I just worked on. When you say "you haven't found one that works successfully with the last version of views 5.x-1.6", what doesn't work? Does the patch not apply? Does it generate errors? Does it not work when you try to add a subquery in a views handler?

That patch works on the 5.x-1.6 version. For the latest dev version, use the attached patch.

One more changed, needed to replace the views_query_substitutions replacements in the subquery...