drupal 7 webform custom views query

Question

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

Answer 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.