More documentation needed about the distinction between Views module and MySQL Database Views

Yeah, this project seems interesting indeed – a drawback of the Webform module is that it doesn't expose the submissions to Views at all.

If I interpret this module description correct, it doesn't allow Views access to the created tables out-of-the-box. (But using the Data module this could be changed.)

Am I right?

Thanks for this contribution!

Yes, "views" is a confusing term in the context of Drupal, since it almost always refers to the Views module, which is basically a web-based UI for building complex queries and displaying their results on a Drupal site.

This module provides MySQL Views, which have absolutely nothing to do with the Views module (I thought I had made a note about that in the README, but on review I see I didn't. I'll add a note to the next release.)

In the meantime: This module does not provide Views integration: it takes Webform submission data and builds flattened MySQL Views for it. In oversimplified terms, you can think of a MySQL View as a read-only table, the purpose of which is easy access to submitted data from outside Drupal.

Webform stores submissions in one monolithic, normalized table: 'webform_submitted_data'. The webform module's logic handles all of the queries and joins necessary to format the submission data in a human-readable way on your Drupal site, but if you have another, external application that wants to import that submission data from your Drupal DB, it would have to replicate those queries and joins itself. What this module does is pre-build a canned query to flatten the submission data and join it with your component definitions, so that you get a MySQL View structured something like

---------------------------------------------------------
|sid|first_name|last_name|street   |city    |state|zip  |
---------------------------------------------------------
|  3|John      |Smith    |123 Main |Anytown |MA   |12345|
---------------------------------------------------------

Which is much more convenient to import than the webform_submitted_data table, which might look like

-------------------------
|nid|sid|cid|no|data    |
-------------------------
|123|  3|  1| 0|John    |
|123|  3|  2| 0|Smith   |
|123|  3|  3| 0|123 Main|
|123|  3|  4| 0|Anytown |
|123|  3|  5| 0|MA      |
|123|  3|  6| 0|12345   |
-------------------------

So, to reiterate: This module does not integrate Webform submissions with Drupal Views. It builds MySQL Views for webform submissions.

More on MySQL Views.

Thanks!

A follow-up question – does this MySQL View get stored in the Drupal database, or is it just temporarily built just when needed? (If the former is the case, the Data module could actually make the table visible in the Views module, which would make you kind of a hero.)

Cheers,

The MySQL view does get permanently built in the Drupal database. (See the webform_mysql_views_get_view_name() function for how the view name is constructed.) Views integration via the Data module hadn't occurred to me - great idea! I'll be interested to see if it works.

i try this but at this time it's not possibile. drupal views needs a primary key and the mysql views created from webform submissions don't have one. i don't know if is possibile to alter this query in order to create a new id field as primary key or tell to views that parent.sid is an auto_increment field or primary key.

$query = "CREATE OR REPLACE VIEW {%s} AS SELECT parent.sid, s.uid,"
. $components
.' FROM_UNIXTIME(s.submitted) AS submitted, s.remote_addr FROM {webform_submitted_data} AS parent JOIN {webform_submissions} s ON s.sid = parent.sid WHERE parent.nid = %d GROUP BY parent.sid ORDER BY parent.sid DESC;';

All right!

I am happy to conclude that it is possible to get the view table into the Views module, by using the Data module. It is a bit trickier than necessary, and I've started a separate issue about how to fix that.

A demonstration of it all works is posted at http://nodeone.se/blogg/finally-webform-submission-data-in-views

Happy days!