Creating Sub Tables with Database Manager

Intro

NOTE: This option is currently available for NodeJS server model only. Support for other server models is coming next weeks.

The sub tables in Wappler provide an easy way to create and access nested data structures much easier. The relational databases make sense now, as you can easily nest data structures in sub tables.
Then you can query these tables and their sub tables without the need of complex queries - Wappler handles it all for your in a single query and builds the required nested queries.

Sub tables can be useful in cases where, for example, you want to store people and phone numbers or blog articles and comments.

Creating Sub Tables

In our case we will show you how to create a blog articles database table and a sub table for the comments for each of the articles. Later when you run a sub query, you will receive the blog article with all the comments related to it in a single database query.

Main Table

Firs let’s create the main table - the blog articles table.
Open the Database Manager and create a new table:
Note that you need to have a database connection setup already.

Right click Tables:

And create a New Table:

We call the table blog_posts:

An id primary key field has been created automatically for it:

We will need to add a few more fields like - title, text, author and date. In order to add a new field, right click the table name and select New Field:

We add a new field called title and set its type to String. This field will store the blog post title:

Then we add another field, called text which will store the blog post text. We set its type to Text:

Then we add another field, called author which will store the blog post author. We set its type to String:

Then we add another field, called date which will store the blog post date. We set its type to Datetime:

So we are pretty much done with the blog post table. We have all the fields we need here for creating a blog post.

Sub Table

Now let’s create a sub table for the blog post comments.
Right click the main table (blog_posts) name and select New Sub Table:

Add a name for it. We call it comments:

An id primary key field has been created automatically. Also, the relations between the blog_posts table and the comments table has also been created automatically by Wappler:

For this table we will need two more fields - comment and email. These will store the comment text and the email of the person who posted the comment.
Right click the table and add a new field:

We call it comment and set its type to Text:

Then we add one more field and call it email. Set its type to String:

And we are done setting up the sub table.

Applying DB Changes

Now let’s apply the changes to the database. Click the Apply Database Changes Button:

Enter a description and click OK:

Now our main table and its sub table are ready to be used:

That’s how easy it is to create sub tables for your nested database structures.

7 Likes

Wow that’s pretty cool! Seems to have some similarities with the way Strapi does it. Super excited to try it out!

2 Likes

The last two updates are what we have been waiting for, thanks.
This O2M relations and M2M on next update? :grinning:

1 Like

Great implementation. Can we make (other) existing table as sub table or join table using this step?

Also I missed tutorial on how to insert database into the sub table.

1 Like

Looks very interesting indeed, will try it out soon.

@Teodor I have lots of ‘sub tables’ right now, how would I go about migrating them to use this feature?

Why don’t I see the “add sub table” link? Wappler is up to date.

Probably you haven’t enabled experimental features in Wappler global options.

Thank you! Ok, now if I have to do an insert query how should I do it? How do I join the 2 tables?

The two queries are automatically “joined” for you.
You just insert your records in the sub query and pass the main query record id there. That’s all. Then the records will be automatically linked to the main item’s ID.

1 Like

This does look interesting but I am trying to figure out when it would be needed to use it? It’s simple enough to set up a Join in the query builders as it is.

Maybe I am just missing the big picture. Why would you use this over just setting up a join in the query Builder? Seems there is more flexibility in the type of Join using the query builder as well?

Just trying to figure out what the big advantage is as I don’t ever use the Database Manager. Maybe I’m missing something game changing.

2 Likes

@brad I was wondering the same. Perhaps it’s handy if the sub table is not going to be related to any other table or not need to stand on it’s own. It’s a great feature for some I’m sure but I’d rather have the simple ability to copy the query at the bottom of the database query builder before this feature haha

Sub tables helps you achieve much better overview of your database structure because you are putting them exactly under the tables they belong to.

So you can clearly see the relation between strictly related tables like order and order_items for example.

As order_items makes no sense on its own it lives always under its parent “order”

So instead of guessing tables prefixes so see a relations, those are now clearly visible in the database tree as nested sub tables.

This makes also automatic query builder joins possible because all the relations are known. So you no longer have to do manual joins and think of matching columns - it is all done for you.

So all for the ease of use :slight_smile:

Thanks George,

If I do a Sub Table, how will it show up in Navicat? Just as another table? As I mention I really don’t use the database Manager in Wappler unless I really need to.

Yes it will show just as any other table but just the name prefixed with the parent table.

This is a standard database convention and we use it also to recognize sub tables from existing databases.

See for example:
https://webdevetc.com/blog/laravel-naming-conventions/#section_naming-database-tables-in-laravel

1 Like

Thanks George, certainly an awesome feature. Users of the DB Manager will find this very useful. Well done.

Would be nice if you deleted a row in your main table, any related rows in the sub tables would also delete to prevent orphaned rows in the sub table.

That is exactly what the delete record action is going to do :slight_smile:

1 Like