Prevent ambiguity in Drupal 8 migration source query

If you customize the database query in Drupal 8 migration source plugins, you may run into an integrity constraint violation error. This can be resolved by setting an alias for the table.

In order to migrate only files attached to nodes of certain content types, we extended the Drupal 8 d6_file migration source plugin from the file module and adapted the database query accordingly:

namespace Drupal\custom_migration\Plugin\migrate\source;

/**
 * Drupal 6 file source from database.
 *
 * @MigrateSource(
 *   id = "d6_file_by_node_type",
 *   source_module = "system"
 * )
 */
class File extends \Drupal\file\Plugin\migrate\source\d6\File {

  /**
   * {@inheritdoc}
   */
  public function query() {
    $query = parent::query();

    if (isset($this->configuration['node_type'])) {
      $query->innerJoin('upload', 'u', 'f.fid = u.fid');
      $query->innerJoin('node', 'n', 'u.nid = n.nid');
      $query->condition('n.type', (array) $this->configuration['node_type'], 'IN');
    }

    return $query;
  }

But running that migration failed because the file id (fid) was now ambiguous:

Integrity constraint violation: 1052 Column fid in on
clause is ambiguous: SELECT f.*, map.sourceid1 AS
migrate_map_sourceid1, map.source_row_status AS
migrate_map_source_row_status
FROM 
{files} f
INNER JOIN {upload} u ON f.fid = u.fid
INNER JOIN {node} n ON u.nid = n.nid
LEFT OUTER JOIN drupal.migrate_map_d6_file map ON fid = map.sourceid1
WHERE (n.type IN (:db_condition_placeholder_0,
:db_condition_placeholder_1, :db_condition_placeholder_2,
:db_condition_placeholder_3)) AND ((map.sourceid1 IS NULL) OR
(map.source_row_status = :db_condition_placeholder_4))
ORDER BY timestamp ASC, f.fid ASC; Array
(
    [:db_condition_placeholder_0] => page
    [:db_condition_placeholder_1] => article
    [:db_condition_placeholder_2] => press_release
    [:db_condition_placeholder_3] => news
    [:db_condition_placeholder_4] => 1
)

Drupal 8 silently alters the query by adding a join to a mapping table in order to look up migration data for the files:

LEFT OUTER JOIN drupal.migrate_map_d6_file map ON fid = map.sourceid1

As the d6_file source plugin doesn't set a table alias for the source field identifier, fid in the above mentioned join is ambiguous. The column exists both in the files and upload database tables. To resolve this just add an alias in MigrateSourceInterface::getIds():

namespace Drupal\custom_migration\Plugin\migrate\source;

/**
 * Drupal 6 file source from database.
 *
 * @MigrateSource(
 *   id = "d6_file_by_node_type",
 *   source_module = "system"
 * )
 */
class File extends \Drupal\file\Plugin\migrate\source\d6\File {

  /**
   * {@inheritdoc}
   */
  public function query() {
    $query = parent::query();

    if (isset($this->configuration['node_type'])) {
      $query->innerJoin('upload', 'u', 'f.fid = u.fid');
      $query->innerJoin('node', 'n', 'u.nid = n.nid');
      $query->condition('n.type', (array) $this->configuration['node_type'], 'IN');
    }

    return $query;
  }

  /**
   * {@inheritdoc}
   */
  public function getIds() {
    $ids = parent::getIds();

    $ids['fid']['alias'] = 'f';

    return $ids;
  }

}