Map profile_id based on previously mapped uid when migrating user profiles from CSV file

I'm trying to migrate users and user profiles to site, where both entities might be already present. In this case I would like to overwrite specified fields and leave others in the original state. I'm trying to map profile_id like this:

  uid:
    plugin: migration_lookup
    no_stub: true
    # previous user migration
    migration: mayors_users
    # property in the source data
    source: lau
  profile_id:
    plugin: entity_lookup
    entity_type: profile
    bundle: profile
    bundle_key: type
    value_key: uid
    source: '@uid'

However, this fails, so profile_id is null.

Do you happen to know, how can I use entity_lookup (or another) plugin for this?

EDIT: It looks like this might be a Drupal Core issue. I debugged the code of EntityLookup.php plugin and printed the actual DB query generated from the entity query:

SELECT base_table.revision_id AS revision_id, base_table.profile_id AS profile_id
FROM profile base_table
         INNER JOIN profile profile ON profile.profile_id = base_table.profile_id
WHERE (profile.uid = '153')
  AND (profile.type = 'profile')

This works fine and return following result when used directly against MySQL server:

However, $query->execute() still returns an empty array. Why?

EDIT: It looks like this might be a bug: https://www.drupal.org/project/migrate_plus/issues/3230477

If I understand your question correctly and it is indeed a bug (so that there is no proper solution to your problem) I can think of at least 2 ways you could work around this.

1. hook_migrate_prepare_row / hook_migrate_MIGRATION_ID_prepare_row

You could use hook_migrate_prepare_row or hook_migrate_MIGRATION_ID_prepare_row to preprocess your source data and fetch the uid and profile id manually, something like this:

/**
 * Implements hook_migrate_MIGRATE_ID_prepare_row().
 */
function my_module_migrate_MIGRATE_ID_prepare_row(Row $row, MigrateSourceInterface $source, MigrationInterface $migration) {
  $raw_data = (object) $row->getSource()['raw'];
  // Here the database queries as needed.
  // $uid = \Drupal::database()->select ...
  // $profile_id = \Drupal::database()->select ...
  $row->setSourceProperty('uid', $uid);
  $row->setSourceProperty('profile_id', $profile_id);
}

Note that Migrate Plus provides an object-oriented alternative to those hooks: https://www.drupal.org/docs/upgrading-drupal/customize-migrations-when-upgrading-to-drupal-8-or-later#s-migrate-plus-provides-a-prepare-row-event

2. Write your own process plugin

There is good documentation on how to write a process plugin on drupal.org: https://www.drupal.org/docs/8/api/migrate-api/migrate-process/writing-a-process-plugin

It basically looks like this:

<?php

namespace Drupal\my_module\Plugin\migrate\process;

use Drupal\migrate\ProcessPluginBase;
use Drupal\migrate\MigrateExecutableInterface;
use Drupal\migrate\Row;

/**
 * Provides a 'ExtractProfileIdFromLau' migrate process plugin.
 *
 * @MigrateProcessPlugin(
 *  id = "extract_profile_id_from_lau"
 * )
 */
class ExtractProfileIdFromLau extends ProcessPluginBase {

  /**
   * {@inheritdoc}
   */
  public function transform($value, MigrateExecutableInterface $migrate_executable, Row $row, $destination_property) {
    // Fetch profile id.
    $profile_id = \Drupal::database()->select ...
    return $profile_id;
  }

}

And it can be referenced in your migration.yml file under the process section, something like this for example:

process:
  profile_id:
    -
      plugin: extract_profile_id_from_lau
      source: lau

Not quite sure which way would work best for you, but those are the 2 ideas that would come to mind. At least that's how I would try it.