most people don't know that, but my first major was in Advertising. although that's a career path I never took, I've always enjoyed a good ad or slogan, like the epic Greatness awaits for the PlayStation or McCann-Erickson's classic Truth well told.

another thing I learned from Advertising — and Design! — is that there is no such thing as a bad cliché¹. to wit, It's Luco's birthday but you're getting the gift!

*ahem* anyway, to celebrate my birthday I decided to work on a TARDIS bug that's been living rent-free in my brain for, well, almost a year (talk about coincidences). and it's a tricky one at that! the issue is that TARDIS² needs to fetch all of your posts in order to build an archive-style list of links to previous content, sorted by year and month.

so how do you do that without going over everything, when you have, say, 10,000 nodes? turns out the solution was deceptively simple: write a subquery that takes one entry for each month, using basic SQL functions:

/**
 * Implements hook_views_query_alter().
 * Reduces the number of results to one per month, improving execution time.
 */
function tardis_views_query_alter(\Drupal\views\ViewExecutable $view, \Drupal\views\Plugin\views\query\QueryPluginBase $query) {
  // This will only work on the base TARDIS view.
  // @todo Turn it into an option for any view.
  if ($view->id() !== 'tardis') {
    return;
  }

  // Get database type for DB-specific functions
  $database = \Drupal::database();
  $db_type = $database->databaseType();

  // DB-specific date functions.
  // @todo Extended options for Created, Updated, or any Date field.
  if ($db_type === 'mysql') {
    $tardis_year = 'YEAR(FROM_UNIXTIME(created))';
    $tardis_month = 'MONTH(FROM_UNIXTIME(created))';
  } elseif ($db_type === 'pgsql') {
    $tardis_year = 'EXTRACT(YEAR FROM TO_TIMESTAMP(created))';
    $tardis_month = 'EXTRACT(MONTH FROM TO_TIMESTAMP(created))';
  } else {
    // SQLite fallback:
    $tardis_year = 'strftime("%Y", datetime(created, "unixepoch"))';
    $tardis_month = 'strftime("%m", datetime(created, "unixepoch"))';
  }

  // Correlated subquery that reduce results to one per month.
  $subquery = "node_field_data.nid = (
    SELECT n2.nid
    FROM {node_field_data} n2
    WHERE $tardis_year = (SELECT $tardis_year FROM {node_field_data} n3 WHERE n3.nid = node_field_data.nid)
      AND $tardis_month = (SELECT $tardis_month FROM {node_field_data} n3 WHERE n3.nid = node_field_data.nid)
    ORDER BY n2.created DESC
    LIMIT 1
  )";

  $query->addWhereExpression(0, $subquery);
}

the improvement even takes into account different DBs: MySQL, Postgres, and SQLite. however, for even better performance, DB tuning should be considered.

caveat, Drupalor

the change needs testing, and as usual, I welcome the gift of collaboration. birthday cake is also accepted.

¹ I forgot who coined that term. if it was you, dear reader, let me know!

² the module, not the time machine.