Views3 and subqueries? I've got a view which generates a query that does multiple joins. This produces a cartesian join, and I need to "convert" the joins to subqueries. 

I've looked through documentation, Google search results and other sources, but I am unable to find any decent description of how I can configure Views to do subqueries. I've used hook_views_data() to configure the relationships (which are now carried out as joins). Is it somehow possible to define subqueries through hook_views_data(), or do I need to take another approach?

Any advise appreciated!
I looked further, but could not really find any documentation describing this. 

What I needed was a way to join the users table with two other tables holding data for the users. However, the two other tables are in a 'one-to-many' relationship with the users table, meaning that I'll end up with a Cartesian join if I attempt to join the users table with both of these tables at the same time. However, since all I need is to count the number of records in the two other tables associated with any given user, a subquery should be able to do the trick. However, I couldn't find any documentation on Views and subqueries - so here is what I did.


Created two dummy fields


I created two dummy fields (which I'll call 'downloads' and 'listens') through hook_views_data(). The definition of the field are listed below.

function hook_views_data() {

  $data['users'] = array(
    'downloads' =&gt; array(
      'title' =&gt; t('Downloads'),
      'field' =&gt; array(
        'handler' =&gt; 'views_handler_field_numeric',
        'click sortable' =&gt; TRUE,
      ),
      'filter' =&gt; array(
        'handler' =&gt; 'views_handler_filter_numeric',
      ),
      'sort' =&gt; array(
        'handler' =&gt; 'views_handler_sort',
      ),
    ),
    'listens' =&gt; array(
      'title' =&gt; t('Listens'),
      'field' =&gt; array(
        'handler' =&gt; 'views_handler_field_numeric',
        'click sortable' =&gt; TRUE,
      ),
      'filter' =&gt; array(
        'handler' =&gt; 'views_handler_filter_numeric',
      ),
      'sort' =&gt; array(
        'handler' =&gt; 'views_handler_sort',
      ),
    )
  ),
);


Now, when you configure a view for users, the fields 'Downloads' and 'Listens' will appear. However, attempting to run a query now will result in an error as the dummy fields after all are dummy fields. They do not exist. The only purpose of these fields is to signal to to our implementation of hook_views_query_alter() that it needs to do a few replacemenets.


Implement hook_views_query_alter()


The trick here is to check wether the given query includes the 'Downloads' or 'Listens' fields. If it does, we'll remove the fields from the query and replace them with subqueries. The implementation of this function goes as below.

function mta_views_query_alter(&amp;$view, &amp;$query) {

  foreach ($query-&gt;fields as $field_key =&gt; &amp;$field_values) {
    if ($field_values['table'] == 'users') {

      switch ($field_values['field']) {
        case 'downloads':
          unset($query-&gt;fields[$field_key]);
          $query-&gt;add_field(null, "(SELECT COUNT(*) FROM {fileusage} fu WHERE fu.externaluser = {users}.uid AND fu.action = 0)", $field_key);
          break;
        case 'listens':
          unset($query-&gt;fields[$field_key]);
          $query-&gt;add_field(null, "(SELECT COUNT(*) FROM {fileusage} fu WHERE fu.externaluser = {users}.uid AND fu.action = 1)", $field_key);
          break;
      }
    }
  }
}


Note that we're reusing the alias of the removed field for the subquery. That way, Views will think that the value returned from the subquery actually comes from the dummy field (which after all does not exist).

That's is. We're not getting a Cartesian join and both 'downloads' and 'listens' are counted correctly.