Django app mySQL database - combine tables

Ask a question
1

Hi everyone,

I have a problem that maybe some of you more savvy users know how to solve. I have inherited a Django website that will be converted to Webflow but the current mySQL database is a bit complex.

So, for example, I have a “books” table and an “authors” table. Since a book can have multiple authors, the way Django works is that is creates a third multi reference table, combining “book_id” with “author_id”. Basically is creates a “books_authors” table in which each row has a “book_id” and an “author_id”. So I have multiple rows with the same “book_id” and several “author_id”.

What I’m trying to do is create a new table that combines information from the 3 tables. Here’s an example:

books
-----------------------------------
+ book_id
+ book_title

authors
-----------------------------------
+ author_id
+ author_name

books_authors
-----------------------------------
+ book_id
+ author_id

new_book_table
-----------------------------------
+ book_id
+ book_title
+ author_name

I’ve been playing around with the different options for mySQL inside Parabola but I can’t find a way to merge these 3 tables into one, that I later want to convert into a CSV file.

Can someone point me in the right direction?

Thank you

2

Hey @zehfred :wave:

Good question. You should be able to use this by using a few Combine tables steps followed by a Merge duplicate rows step. Your flow might look like this:

my-books-flow
my-books-flow2536×1694 438 KB

Combine your Books table with your Books_Authors table. Rows match if the book_id from your Books table is equal to your book_id from your Books_Authors table. The resulting table should have the structure of your Books table with an additional author_id column.

We can do another Combine tables step to join that table with our Authors table. Same thing here, we’ll want to combine on the shared author_id column.

Your table should now duplicate each book row for every associated author. To format this, merge the author_name column per unique value in the book_id column. You can then use a Select columns step to keep or remove any columns you choose before exporting.

3

Hi @daniel ,

Thank you so much for this! I think it’s exactly what I was looking for :))

1 Like