Importing repeat groups into mysql

Support
, ,
1

Hi all.

I am struggling slightly with repeat grouping questions / collected data.

Currently I have a survey form setup with NO repeat groups, which is working wonderfully. This gets pulled into Aggregate, and from there I use Briefcase to export it to CSV, from CSV into Mysql DB, storing text and reference/ links to the media images in the DB.

However I want to start making use of the repeat group feature, I have noticed that with a repeat group, and individual csv file gets created for each group - which is a problem for me importing that file into my mysql DB. The way its setup is that each form submission imports into its own row into mysql, but I cannot get my head around as to how I can add "multiple rows for each submission" into_ mysql.

Currently I am designing my form in such a way that I do not need repeat groups, but in future it is inevitable that I will need this feature.

Please can somebody assist in how add the individual repeat groups into my mysql db,

1 Like
2

Hi, @Clinton_P!

First, you need to create one table per CSV file you want to import.

Once all data from the main CSV file and all the repeat CSV files is imported into tables in your MySQL database, you can select rows using the SQL JOIN syntax

For example, let's imagine we have a house form with a human repeat group in it. Then you can get all rows for all houses and all their human habitants with the following query:

SELECT * 
FROM house 
LEFT JOIN human ON house.key = human.parent_key;

Let's imagine that the human repeat group has another nested task repeat group. Then, you can get all rows for all houses, all their human habitants, and all their tasks with the following query:

SELECT * 
FROM house 
LEFT JOIN human ON house.key = human.parent_key
LEFT JOIN task ON human.key = task.parent_key;

As you can see, tables are related using their key and parent_key columns.

This works well when there's just one chain of nested repeat groups, but sometimes we have separate chains of repeat groups.

Let's imagine that the form has another pet repeat group. Then, we change the query a little bit:

SELECT *
FROM house 
LEFT JOIN human ON house.key = human.parent_key;
LEFT JOIN pet ON house.key = pet.parent_key;

In this case, some sets of columns belonging to the human or pet tables will be repeated in your results, because there will probably be different cardinalities between house <> human and house <> pet. If this happens, I'd advise making two separate queries:

SELECT * 
FROM house 
LEFT JOIN human ON house.key = human.parent_key;

and

SELECT * 
FROM house 
LEFT JOIN pet ON house.key = pet.parent_key;
2 Likes
3

This approach produces duplicate results as the query creates a new row for each left-right match.. Is there a way to avoid this?