0

I have a custom hook_views which queries db for a webform submission. the query looks like this

$query = db_select('webform_submitted_data','n');
    $query->leftJoin('webform_component', 'wc', 'wc.cid = n.cid');
    $query->fields('n', array('sid','data','cid'));
    $query->fields('wc', array('form_key','cid'))
    ->condition('n.nid',25)

Which translates to mysql:

select n.sid,n.cid,n.data,wc.form_key,wc.cid from webform_submitted_data n left join webform_component wc on wc.cid = n.cid where n.nid = 25

Now i have made a new field called status whose value can be 1 or 0, i want to show only those form submission whose status values are 1, I tried the following query in phpmyadmin but it gives out only 1 row output where the form_key value = status and data =1, where as the earlier query gave all the form_key, and data values for form id 25.

select n.sid,n.cid,n.data,wc.form_key,wc.cid from webform_submitted_data n left join webform_component wc on wc.cid = n.cid where n.nid = 25 and wc.form_key="status" and n.data =1

Is their any way by which i can add a views filter like condition here to show only data where status = 1

1

Webform's schema makes it fairly painful, but this should fetch all of the submitted data for any submission where the field with key status has a value of 1:

$nid = 25;
$form_key = 'status';
$data = '1';

$query = db_select('webform_submitted_data','n');
$query->leftJoin('webform_component', 'wc', 'wc.cid = n.cid AND wc.nid=n.nid');
$query->innerJoin('webform_component', 'wc_filter', "wc_filter.nid=n.nid AND wc_filter.form_key=:form_key", array(':form_key' => $form_key));
$query->innerJoin('webform_submitted_data', 'n_filter', 'wc_filter.cid = n_filter.cid AND n.sid=n_filter.sid AND n_filter.data=:data', array(':data' => $data));

$query->fields('n', array('sid','data','cid'));
$query->fields('wc', array('form_key','cid'))
  ->condition('n.nid', $nid);

There may be performance implications doing this if you have more than a few records...

You could simplify a bit by looking up ahead of time the cid of the data component, or just hardcoding it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.