Hi,

I'm trying to use entity query to select webformsubmisisons that contain a value in a textfield. My question is how should I call the data-fields?

If I use this condition (ticket_for = field name):
->condition('data.ticket_for',$ADPlusName);
I get this error:

Error: Call to a member function getColumns() on boolean in Drupal\Core\Entity\Query\Sql\Tables->addField() (line 227 of /www/sites/chiro8/chiroD8/web/core/lib/Drupal/Core/Entity/Query/Sql/Tables.php) 
#0 /www/sites/chiro8/chiroD8/web/core/lib/Drupal/Core/Entity/Query/Sql/Condition.php(44): Drupal\Core\Entity\Query\Sql\Tables->addField('data.ticket_for', 'INNER', NULL) 
#1 /www/sites/chiro8/chiroD8/web/core/lib/Drupal/Core/Entity/Query/Sql/Query.php(155): Drupal\Core\Entity\Query\Sql\Condition->compile(Object(Drupal\Core\Database\Driver\mysql\Select)) 
#2 /www/sites/chiro8/chiroD8/web/core/lib/Drupal/Core/Entity/Query/Sql/Query.php(74): Drupal\Core\Entity\Query\Sql\Query->compile() 
#3 /www/sites/chiro8/chiroD8/web/modules/custom/chiro_events/chiro_events.module(165): Drupal\Core\Entity\Query\Sql\Query->execute() 
#4 /www/sites/chiro8/chiroD8/web/modules/custom/chiro_events/chiro_events.module(88): registeredCheck('9', 'Jon Jacobs-1693...') 
#5 /www/sites/chiro8/chiroD8/web/core/lib/Drupal/Core/Extension/ModuleHandler.php(501): chiro_events_form_alter(Array, Object(Drupal\Core\Form\FormState), 'webform_submiss...') 
#6 /www/sites/chiro8/chiroD8/web/core/lib/Drupal/Core/Form/FormBuilder.php(818): Drupal\Core\Extension\ModuleHandler->alter('form', Array, Object(Drupal\Core\Form\FormState), 'webform_submiss...')

These condtions:

->condition('data',$ADPlusName,'CONTAINS');
->condition('data_ticket_for',$ADPlusName);
->condition('data:ticket_for',$ADPlusName);

Give this error:
Drupal\Core\Entity\Query\QueryException: 'data' not found in Drupal\Core\Entity\Query\Sql\Tables->ensureEntityTable() (line 316 of /www/sites/chiro8/chiroD8/web/core/lib/Drupal/Core/Entity/Query/Sql/Tables.php).

Kind regards and thanks for a fantastic job on the webform module,

Janec

Comments

Janec created an issue. See original summary.

jrockowitz’s picture

jrockowitz CreditAttribution: jrockowitz as a volunteer and at The Big Blue House commented
Status: Active » Closed (works as designed)

WebformSubmission data is not stored using Field API. @see #2792583: Use Field API

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

taggartj’s picture

taggartj CreditAttribution: taggartj as a volunteer commented

Yep but to save you or anyone who is searching for this (means they want to know how to do it) 

/**
 * Helper function to load submission by data key.
 *
 * @param string $web_form_id
 *   The webform id key.
 * @param string $data_key
 *   The element key like 'email'.
 * @param string $value
 *   The element value.
 *
 * @return array
 *   This will return an array;
 */
function _mymodule_webform_submission_by_data_key($web_form_id, $data_key, $value) :array {
  $database = \Drupal::service('database');
  $select = $database->select('webform_submission_data', 'wsd')
    ->fields('wsd', array('sid'))
    ->condition('wsd.webform_id', $web_form_id, '=')
    ->condition('wsd.name', $data_key, '=')
    ->condition('wsd.value', $value, '=');
  $executed = $select->execute();
  // Get all the results.
  $results = $executed->fetchAll(\PDO::FETCH_ASSOC);
  if (count($results) == 1) {
    $results = reset($results);
  }
  return $results;
}

//use like 
$sids = _mymodule_webform_submission_by_data_key('contact', 'email', 'email@test.com');
if (count($sids == 1) {
 // load that one
 $sid = $sids['sid'];
}
vunda’s picture

vunda CreditAttribution: vunda as a volunteer commented

This code works only Drupal 7 (array). Any suggestions for Drupal 8. Also, I wondered about memory, maybe json is the best approach.

imclean’s picture

imclean CreditAttribution: imclean commented

#2

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

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 = 'staff_shift'
     AND sid IN (SELECT sid FROM webform_submission_data wsd2                   
                  WHERE wsd2.value = 3
                    AND wsd2.name = 'staff_member')
     AND sid IN (SELECT sid FROM webform_submission_data wsd3
                  WHERE wsd3.value = '2018-08-10AEST12:06:29'
                    AND wsd3.name = 'shift_start');

Example static query, supplying the user ID as a variable:

$query = $connection->query("SELECT DISTINCT sid
   FROM webform_submission_data wsd
     WHERE wsd.webform_id = 'staff_shift'
     AND sid IN (SELECT sid FROM webform_submission_data wsd2                   
                  WHERE wsd2.value = :uid
                    AND wsd2.name = 'staff_member')
     AND sid IN (SELECT sid FROM webform_submission_data wsd3
                  WHERE wsd3.value = '2018-08-10AEST12:06:29'
                    AND wsd3.name = 'shift_start')", [
  ':uid' => $uid,
]);
imclean’s picture

imclean CreditAttribution: imclean commented

I've created a sandbox module Webform Query to test an idea. It's very basic at the moment but it makes it easier to select submissions by querying submission data.

jrockowitz’s picture

jrockowitz CreditAttribution: jrockowitz as a volunteer and at The Big Blue House commented

@mclean That is an awesome idea.

I added the Webform Query sandbox module to the Webform's list of add-ons.