Useful procedure to generate a full export of ALL form submissions directly in SQL

[lbradley-mm]

DROP PROCEDURE IF EXISTS UserForm_ExportAllSubmissions;
DELIMITER ;;

CREATE PROCEDURE UserForm_ExportAllSubmissions(IN debug INT(1))
COMMENT 'Will generate CSV exportable tables for each page. Columns are dynamically created so each table needs to be exported independently via a client (e.g. SQLPro)'
BEGIN

DECLARE form_id VARCHAR(255);
DECLARE st_title VARCHAR(255);
DECLARE st_done INT DEFAULT FALSE;
DECLARE form_cursor CURSOR FOR SELECT ID, Title FROM SiteTree_Live WHERE ClassName IN ("UserDefinedForm", "FormPage");
DECLARE CONTINUE HANDLER FOR NOT FOUND SET st_done = TRUE;
OPEN form_cursor;
read_loop: LOOP
	-- Loop over all the forms	
	FETCH form_cursor INTO form_id, st_title;
	IF st_done THEN
		LEAVE read_loop;
	END IF;
	
	SET @select = CONCAT('SELECT "', st_title, '" As "Form", `sub`.`ID`');
	SET @join = '';
	
	FIELDS_BLOCK: BEGIN
		DECLARE names_done INT DEFAULT FALSE;
		DECLARE f_name VARCHAR(255);
		DECLARE f_title VARCHAR(255);
		DECLARE fields_cursor CURSOR FOR SELECT Name, CASE WHEN Title IS NOT NULL THEN Title ELSE Name END AS Title FROM SubmittedFormField WHERE ParentID IN (SELECT ID FROM SubmittedForm WHERE ParentID = form_id) GROUP BY Name;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET names_done = TRUE;
		OPEN fields_cursor;
		read_fields: LOOP
			-- Loop over all fields in form
			FETCH fields_cursor INTO f_name, f_title;
			IF names_done THEN
				LEAVE read_fields;
			END IF;
			SET @select = CONCAT(@select, ',\n`', f_name, '`.`Value` as "', f_title, ' (', f_name, ')"');
			SET @join = CONCAT(@join, '\nLEFT JOIN SubmittedFormField As ', f_name, ' ON (', f_name, '.ParentID = sub.ID AND ', f_name, '.Name = "', f_name, '")');
		END LOOP;
		CLOSE fields_cursor;
	END FIELDS_BLOCK;
	
	SET @sql = CONCAT('-- Export for Form ', form_id, '\n', @select, '\nFROM SubmittedForm AS sub ', @join, '\nWHERE sub.ParentID = ', form_id);

	IF debug THEN 
		SELECT @sql AS 'SQL being run to generate the table';
	END IF;
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
END LOOP;
CLOSE form_cursor;
END;;


DELIMITER ;
CALL UserForm_ExportAllSubmissions(1);

I made a start on this as Full CSV exports can time out if there are a LOT of submissions

[michalkleiner]

Thanks for opening the issue, @lbradley-mm.

What would you see as the outcome here, would it be added to some README or other documentation, or you think it should be somehow part of the code, or something else?

For exports that time out, the standard approach that I would recommend is using a queued job that runs a build task which will either email the exported CSV (as there's no time limit for queued jobs), or store it in the file system/assets section somewhere for future access etc.

Writing a db procedure might be a cool trick, but it circumvents all the CMS/ORM level checks, data transformations, extensions etc etc, so it's not really a full-featured replacement. Also different db backends may treat the SQL code differently and it may not be compatible with different db engines.

With that, I will close the issue for now as I don't see an easy way how this can be used or integrated by the module itself. Please do feel free to leave further comments or e.g. a link to a gist with the procedure so that people can use it for inspiration should they come across the same need.

Thank you for your understanding and time creating the issue 👍!

[lbradley-mm]

Hi @michalkleiner
I'd say you're right that it's probably just a useful tool for exports that may go into documentation or similar
The idea of queuing and emailing an export would probably be the best solution for the scenario I currently have
Thanks