Entity Query field condition for Webform submissions

Question

I'm trying to query webform submissions based on a field value. The field is called "ticket_for". Somethings I tried and do not work:

    ->condition('ticket_for',$adnummer)
    ->condition('webform_submission_data:ticket_for',$adnummer,'CONTAINS')
...

What kind of condition should I be using in a query like this?

$query = \Drupal::entityQuery('webform_submission')
->condition('webform_id', 'ticket')
->condition('entity_id', $eventID)
->condition('webform_submission_data:ticket_for',$adnummer,'CONTAINS');

Answer 1

As imclean says webform_submission_data stores as EAV model. You can also do the following, If you need to have just one condition ticket_for == $adnummer.

$select = \Drupal::service('database')
->select('webform_submission_data', 'wsd')
->fields('wsd', array('sid'))
->orderBy('wsd.sid', 'DESC')
->condition('wsd.webform_id', 'your_webform_id', '=')
->condition('wsd.name', 'ticket_for', '=')
->condition('wsd.value', $adnummer, '=')
->execute();

$results = $select->fetchAll(\PDO::FETCH_COLUMN);
if ($results != null && count($results) >= 1) {
    $storage = \Drupal::entityTypeManager()->getStorage('webform_submission');
    $submissions = $storage->loadMultiple($results);    
}

Now just loop the $submissions. Done.

Answer 2

Quoting @jrockowitz:

WebformSubmission data is not stored using Field API (see: #2792583).

You would have to query the webform_submission_data table and collect the submission ids (sid) or use the Webform Views module.

Source: Entity query webform submission data condition.

Answer 3

The table "webform_submission_data" uses the EAV model. To extract useful results you can use a static query.

Example SQL to the get sid meeting all the requirements:

SELECT DISTINCT sid
   FROM webform_submission_data wsd
     WHERE wsd.webform_id = 'ticket'
     AND sid IN (SELECT sid FROM webform_submission_data wsd2                   
                  WHERE wsd2.name = 'ticket_for'
                    AND wsd2.value CONTAINS '$adnummer')
     AND sid IN (SELECT sid FROM webform_submission_data wsd3
                  WHERE wsd3.name = 'entity_id'
                    AND wsd3.value = $eventID);

Example static query:

$query = $connection->query("SELECT DISTINCT sid
   FROM webform_submission_data wsd
     WHERE wsd.webform_id = 'ticket'
     AND sid IN (SELECT sid FROM webform_submission_data wsd2                   
                  WHERE wsd2.name = 'ticket_for'
                    AND wsd2.value CONTAINS :adnummer)
     AND sid IN (SELECT sid FROM webform_submission_data wsd3
                  WHERE wsd3.name = 'entity_id'
                    AND wsd3.value = :event_id)", [
  ':adnummer' => $adnummer,
  ':event_id' => $eventID
]);

Update 19 October 2018

I've created a module to make it easier to query webform submissions by submission field values. See: https://www.drupal.org/project/webform_query

Answer 4

@imclean's Webform Query is truly a helper. But I wanted to add another alternative here:

1. Use the Webform Views Integration module.
2. Build a view to query the submissions you need.
3. Configure the view to output fields: the webform submission ID field.
4. In the advanced settings of the view under query options disable SQL rewriting to prevent access checks.
5. Programmatically execute the view.
6. And then do whatever you want with the results array.

---

use Drupal\views\Views;

$view = Views::getView('MY_WEBFORM_SUBMISSIONS_VIEW_ID');
$view->setDisplay('default'); // Or any other display ID.
$view->execute();
$results = $view->result;

Answer 5

You can do something like this

$query = \Drupal::entityQuery('webform_submission')
            ->condition('sid', $submission_id, '='); // put the rest of condition here

// this only returns the id of the submission(s)
$result = $query->execute();


// then you can get the rest of the submission data with
$result_data = WebformSubmission::loadMultiple($result);