How to build my SQL statement to export all webform submissions using Forena? I am using Drupal 7.54. and my users are grouped together through a custom user field.

I want to export all webform's submissions for a given group of users in one CSV.

So as suggested in the post How do I programmatically export all webform submissions into a single csv?, I plan to use a custom datablock that fits my need.

I am new to Drupal and don't have much knowledge about how it works and how Drupal stores custom fields in the database.

I couldn't build the right SQL.

What would be the appropriate sql statement in this context?
Have a look at this example SQL, which is a so called "Data Block" I created using the Forena module (disclosure: I'm a co-maintainer), and starting from relative path admin/structure/forena/data:

SELECT s.nid AS node, s.sid AS submission, d.cid AS component, d.data AS fieldvalue
FROM {webform_submissions} s JOIN {webform_submitted_data} d ON s.sid=d.sid 
WHERE d.cid=3 AND s.nid=5
ORDER BY s.sid ASC


Some details about the example I'm using here:


5 in the WHERE-clause is the node id of the webform (which I can edit via node/5/edit). For this webform, I want to query some of its submissions (= results (which I can browse via node/5/webform-results).
3 in the WHERE-clause is the component id of the webform (which I can edit via node/5/webform/components/3). This particular component is configured as an eMail field, and these collected eMail IDs is what I want to export to a CSV file.


Adapt the hardcoded values 3 and 5 (in the WHERE clause) to fit your requirements. Or transform them in parameters contained in a data block (use this link if you wonder how to do that). Then save this data block with whatever name you want, eg drupal_cust/webform_submissions_data.

Use it as a prototype to build your custom report from it, using the Forena Report designer in which you select the webform_submissions_data data block mentioned above. Which just a few click you could create a report with a "layout" for which the design (in XHTML format) looks like so (also note the "tokens" shown within the td-tags):

&lt;body&gt;
  &lt;div id="webform_submissions_data_block" class="FrxTable" frx:block="drupal_cust/webform_submissions_data"&gt;
    &lt;table&gt;
      &lt;thead&gt;
        &lt;tr&gt;
          &lt;th&gt;node&lt;/th&gt;
          &lt;th&gt;submission&lt;/th&gt;
          &lt;th&gt;component&lt;/th&gt;
          &lt;th&gt;fieldvalue&lt;/th&gt;&lt;/tr&gt;&lt;/thead&gt;
      &lt;tbody&gt;
        &lt;tr id="webform_submissions_data" frx:foreach="*"&gt;
          &lt;td&gt;{node}&lt;/td&gt;
          &lt;td&gt;{submission}&lt;/td&gt;
          &lt;td&gt;{component}&lt;/td&gt;
          &lt;td&gt;{fieldvalue}&lt;/td&gt;
        &lt;/tr&gt;
      &lt;/tbody&gt;
    &lt;/table&gt;
  &lt;/div&gt;
&lt;/body&gt;


Don't forget to select the "document types" you want to enable (to export the report), such as "CSV" (as in your question).

For the sake of completeness: the "layout" of a report is what is contained with the &lt;body&gt; and &lt;/body&gt; tag. Similar to a regular HTML file, Forena will add an appropriate &lt;head&gt; and &lt;/head&gt; tag in front of it, and surround everything with  &lt;html&gt; and &lt;/html&gt; tag (and also adds a few more lines at the beginning, to make it an XML file. So that when you save your report specifications, the entire file gets saved in your file system as a so called FRX file (FRX = Forena Report XML). For the above report layout, this is how the corresponding FRX file looks like:

&lt;?xml version="1.0" encoding="UTF-8"?&gt;
&lt;!DOCTYPE root [
&lt;!ENTITY nbsp "&amp;#160;"&gt;
]&gt;
&lt;html xmlns:frx="urn:FrxReports"&gt;
&lt;head&gt;
&lt;title&gt;Webform Export&lt;/title&gt;
&lt;frx:category&gt;Webform&lt;/frx:category&gt;
&lt;frx:options/&gt;
&lt;frx:parameters&gt;
&lt;/frx:parameters&gt;
&lt;frx:docgen&gt;
  &lt;frx:doc type="csv"/&gt;
  &lt;frx:doc type="pdf"/&gt;
&lt;/frx:docgen&gt;
&lt;frx:fields&gt;
&lt;/frx:fields&gt;
&lt;frx:menu/&gt;
&lt;frx:cache/&gt;
&lt;style/&gt;
&lt;/head&gt;
&lt;body&gt;
  &lt;div id="webform_submissions_data_block" class="FrxTable" frx:block="drupal_cust/webform_submissions_data"&gt;
    &lt;table&gt;
      &lt;thead&gt;
        &lt;tr&gt;
          &lt;th&gt;node&lt;/th&gt;
          &lt;th&gt;submission&lt;/th&gt;
          &lt;th&gt;component&lt;/th&gt;
          &lt;th&gt;fieldvalue&lt;/th&gt;&lt;/tr&gt;&lt;/thead&gt;
      &lt;tbody&gt;
        &lt;tr id="webform_submissions_data" frx:foreach="*"&gt;
          &lt;td&gt;{node}&lt;/td&gt;
          &lt;td&gt;{submission}&lt;/td&gt;
          &lt;td&gt;{component}&lt;/td&gt;
          &lt;td&gt;{fieldvalue}&lt;/td&gt;
        &lt;/tr&gt;
      &lt;/tbody&gt;
    &lt;/table&gt;
  &lt;/div&gt;
&lt;/body&gt;
&lt;/html&gt;


For the report we designed here, apart from that table in the report body, about the only extra spec we entered in this example is that we want to enable export options for format "CSV" and "PDF". That's what's indicated via what's between the &lt;frx:docgen&gt; and &lt;/frx:docgen&gt; tag. And since we don't use things like report parameters, menu items, special fields, etc, the corresponding frx tags (frx:parameters, frx:menu, frx:fields, etc) have no values specified in FRX file.

Bonus tip: for this scenario, because of the hardcoded values 5 and 3 (in the WHERE clause of the SQL), the report columns titled node and component are always the same (= 5 and 3). So if you'd want to quickly get rid of those columns in the report, just delete their corresponding lines in the FRX files (with the th and td tags), using your favorite editor (no need to go back to the WYSIWYG report editor, unless you prefer to use that editor). Save the FRX file, and refresh your report ... done.

That's about it ... Sorry the above doesn't give you an idea about how it is like to prototype your data block, or to design your actual report using that data block.