How to perform a webform_submission query with a conditional based on a custom field in the submission data

Question

I'm attempting to query all webforms with a specific id, and sorted by a boolean field that was added to a webform by me in the webform configuration manager. Below is my attempt

    $submission_ids = \Drupal::entityQuery('webform_submission')
        ->condition('webform_id', $webform_id)
        ->exists('paid')
        ->sort('paid', 'desc')
        ->sort('created', 'desc')
        ->pager(50)
        ->execute();

However when I perform this query I get

Drupal\Core\Entity\Query\QueryException: 'paid' not found in Drupal\Core\Entity\Query\Sql\Tables->ensureEntityTable() (line 316 of core/lib/Drupal/Core/Entity/Query/Sql/Tables.php).

If I omit the ->sort('paid','desc'), but keep the ->sort('created', 'desc') the query works fine. I'm assuming this is because created is one of the base fields defined in the webform entity.

I also tried using 'field.paid', and 'data.paid' as a guess but I ended up with

Call to a member function getColumns() on boolean in /var/www/aaep.org/http/web/core/lib/Drupal/Core/Entity/Query/Sql/Tables.php on line 228

Is there a way to sort webform submissions by user defined fields in an entity query?

Answer 1

You first need to select the Submission IDs (SIDs) from the table webform_submission_data:

$select = \Drupal::service('database')
  ->select('webform_submission_data', 'wsd')
  ->fields('wsd', array('sid'))
  ->orderBy('wsd.sid', 'DESC')
  ->condition('wsd.webform_id', $webform_id, '=')
  ->condition('wsd.name', 'paid', '=')
  ->condition('wsd.value', 1, '=')// Not sure how your BOOL value is saved
  ->execute();

// Get all the results.
$results = $select->fetchAll(\PDO::FETCH_COLUMN);

Then you need to use entity_load_multiple() or similar to load the entities:

if (count($results) >= 1) {
  $submissions = \Drupal::entityTypeManager()
    ->getStorage('webform_submission')
    ->loadMultiple($results);

  foreach ($submissions as $submission) {
    // Do something
  }
}

Drupal.org thread Reference for basis of my answer.