Not working with search_api

Thanks @weseze for that code!

I found that it duplicated the existing "random seed" sort I had added to the View, calling it random_order_field_2.

That made the think that might be better to look for such a filter already existing, and to modify it, rather than adding a new filter to the view "behind the scenes".

Here's my version of the code:

/**
 * Implements hook_query_alter().
 */
function random_seed_sort_db_query_alter(Drupal\Core\Database\Query\AlterableInterface $query) {
  // Modify any existing random_order_field sort.
  /** @var \Drupal\Core\Database\Driver\mysql\Select $query */
  $order_by = $query->getOrderBy();
  $needs_seed = (array_key_exists('random_order_field', $order_by));
  if ($needs_seed) {
    // Add random sorting with seed by manually calling the service.
    $options = [
      'id' => 'random_seed',
      'table' => 'views',
      'field' => 'random_seed',
      'relationship' => 'none',
      'group_type' => 'group',
      'admin_label' => NULL,
      'order' => 'ASC',
      'exposed' => NULL,
      'expose' => [
        'label' => NULL,
      ],
      'reuse_seed' => NULL,
      'user_seed_type' => 'same_per_user',
      'anonymous_session' => NULL,
      'reset_seed_int' => 0,
      'reset_seed_custom' => 3600, // time in seconds for the seed renewal.
      'plugin_id' => 'views_random_seed_random',
    ];

    /* @var $random_sort \Drupal\views_random_seed\Plugin\views\sort\ViewsRandomSeedRandom */
    $seed = \Drupal::service('views_random_seed.seed_calculator')
      ->calculateSeed(
        $options,
        'MYVIEWNAME', // This is not correct, but only important for cache tag naming.
        'MYDISPLAYNAME', // This is not correct, but only important for cache tag naming.
        \Drupal::database()->driver()
      );
    // Replace rand() with rand(seed).
    $expressions =& $query->getExpressions();
    $expressions['random_order_field']['expression'] = 'rand(' . $seed . ')';
  }
}

This works with MariaDB/MySQL, not sure if it's reliable for all databases.

Code from weseze at #7 updated by fonant at #9 works for me using Drupal 9.5.11 and Search API with a DB backend.
This is a lifesaver for the project I need random sorting on a search page.

I was wondering if it could be upgraded to get the actual reset_seed_custom value from the view itself rather than hard coding it in the custom code.

@PhilY I don't think that's possible, since we don't have direct access to the view in the query alter.