1 views_handler_relationship_groupwise_max.inc views_handler_relationship_groupwise_max::left_query($options)

Generate a subquery given the user options, as set in the options. These are passed in rather than picked up from the object because we generate the subquery when the options are saved, rather than when the view is run. This saves considerable time.


$options: An array of options:

  • subquery_sort: the id of a views sort.
  • subquery_order: either ASC or DESC.

Return value

The subquery SQL string, ready for use in the main query.:


core/modules/views/handlers/views_handler_relationship_groupwise_max.inc, line 183
Relationship for groupwise maximum handler.


Relationship handler that allows a groupwise maximum of the linked in table. For a definition, see: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.... In lay terms, instead of joining to get all matching records in the…


function left_query($options) {
  // Either load another view, or create one on the fly.
  if ($options['subquery_view']) {
    $temp_view = views_get_view($options['subquery_view']);
    // Remove all fields from default display
  else {
    // Create a new view object on the fly, which we use to generate a query
    // object and then get the SQL we need for the subquery.
    $temp_view = $this->get_temporary_view();

    // Add the sort from the options to the default display.
    // This is broken, in that the sort order field also gets added as a
    // select field. See http://drupal.org/node/844910.
    // We work around this further down.
    $sort = $options['subquery_sort'];
    list($sort_table, $sort_field) = explode('.', $sort);
    $sort_options = array('order' => $options['subquery_order']);
    $temp_view->add_item('default', 'sort', $sort_table, $sort_field, $sort_options);

  // Get the namespace string.
  $temp_view->namespace = (!empty($options['subquery_namespace'])) ? '_' . $options['subquery_namespace'] : '_INNER';
  $this->subquery_namespace = (!empty($options['subquery_namespace'])) ? '_' . $options['subquery_namespace'] : 'INNER';

  // The value we add here does nothing, but doing this adds the right tables
  // and puts in a WHERE clause with a placeholder we can grab later.
  $temp_view->args[] = '**CORRELATED**';

  // Add the base table ID field.
  $views_data = views_fetch_data($this->definition['base']);
  $base_field = $views_data['table']['base']['field'];
  $temp_view->add_item('default', 'field', $this->definition['base'], $this->definition['field']);

  // Add the correct argument for our relationship's base
  // ie the 'how to get back to base' argument.
  // The relationship definition tells us which one to use.
  $this->definition['argument table'], // eg 'term_node',
  $this->definition['argument field'] //  eg 'tid'

  // Build the view. The creates the query object and produces the query
  // string but does not run any queries.

  // Now take the SelectQuery object the View has built and massage it
  // somewhat so we can get the SQL query from it.
  $subquery = $temp_view->build_info['query'];

  // Workaround until http://drupal.org/node/844910 is fixed:
  // Remove all fields from the SELECT except the base id.
  $fields = &$subquery->getFields();
  foreach (array_keys($fields) as $field_name) {
    // The base id for this subquery is stored in our definition.
    if ($field_name != $this->definition['field']) {

  // Make every alias in the subquery safe within the outer query by
  // appending a namespace to it, '_inner' by default.
  $tables = &$subquery->getTables();
  foreach (array_keys($tables) as $table_name) {
    $tables[$table_name]['alias'] .= $this->subquery_namespace;
    // Namespace the join on every table.
    if (isset($tables[$table_name]['condition'])) {
      $tables[$table_name]['condition'] = $this->condition_namespace($tables[$table_name]['condition']);
  // Namespace fields.
  foreach (array_keys($fields) as $field_name) {
    $fields[$field_name]['table'] .= $this->subquery_namespace;
    $fields[$field_name]['alias'] .= $this->subquery_namespace;
  // Namespace conditions.
  $where = &$subquery->conditions();
  $this->alter_subquery_condition($subquery, $where);
  // Not sure why, but our sort order clause doesn't have a table.
  // TODO: the call to add_item() above to add the sort handler is probably
  // wrong -- needs attention from someone who understands it.
  // In the meantime, this works, but with a leap of faith...
  $orders = &$subquery->getOrderBy();
  foreach ($orders as $order_key => $order) {
    // But if we're using a whole view, we don't know what we have!
    if ($options['subquery_view']) {
      list($sort_table, $sort_field) = explode('.', $order_key);
    $orders[$sort_table . $this->subquery_namespace . '.' . $sort_field] = $order;

  // The query we get doesn't include the LIMIT, so add it here.
  $subquery->range(0, 1);

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

  // 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);

  return $subquery_sql;