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 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 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 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 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 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 commented

@mclean That is an awesome idea.

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