Sub-tables / Nested tables not available for join any more

Wappler Version : 5.1.1
Operating System : W10
Server Model: NodeJS
Database Type: PostgreSQL

Expected behavior

When adding a table to join, all tables in the DB should be shown.

Actual behavior

Randomly, if a table has been identified as a subtable using foreign key relation, it stops showing up in the list of table for join.

How to reproduce

No set way to reproduce this. For a table in my DB, I have multiple subtables showing up like that due to foreign keys. But after selecting the main table, one of the subtables does not show up in the list of table to select for a join. I have no option but to add that as a sub table and use index [0] in binding - where a simple join should have worked.

Could you try the latest Wappler version?

If you use the new experimental query builder, it will also change the structure of saved server actions file for Postgres, so make sure that you backup or use git if you plan to revert.

@George Updated to 5.2.2 recently. Still not seeing nested tables in join dropdown.
Not using experimental.

The problem with this is that we have to create multiple query steps to get all the required data, or just use custom query. Please see if this can be fixed soon.

The dropdown should show all tables that have some relation to the already selected table. So the subtable should have a foreign key to the parent table.

It should show above the other tables like:
image

What is the relation between the main table and the table you want to join with?

This is the dropdown when no table is selected.
image
Once I select another table p_order_invoice, the details table (original name t_order_details) is no where to be found in the dropdown to do a JOIN.

image
Both tables have a foreign key reference with the t_orders table’s ID. So they are not directly related - but have an indirect relation.

If they don’t have a direct relation, how do you join them? Shouldn’t you not first join the t_orders table and then join the t_order_details.

Since they both have t_order table’s order_id, I do not have to join t_order first. The join can directly be done on table1.order_id = table2.order_id.

Bump. Ran into the same issue again on Wappler 5.3.1, no resolution yet.
@patrick

Bumped into this today.

I have a common notification table that drives all notifications by using an entity_id. Sometimes this entity_id points to a posts table, other times, a project_documents table. This provides an extensible notification system without having to constantly add new direct relationships, and thus modify the notification code.

So there is no foreign key from entity_id to the id of the entity table (posts, project_documents, etc.).

But this is still a valid join,.

The tables that are missing in the dropdown, are they subtables in the database manager? The dropdown should show all direct relations and under it all remaining tables as you see them in the database manager.

Yes, the table shows up as subtable in DB manager, and also in the query builder as you can see in the above message. I can add the table but only as the first table in query.
I cannot select that table to use as join, which is the bug.

I have these issues so much that I default to custom queries now. Wappler should stop trying to be clever based on column names or indices. Your job become much simpler and so do ours as we Build significant context into our database and only we know how our table joins work best - and need the freedom our evil minds demand to work this way.

This is not a criticism. Just my thoughts.

Does this mean you also don’t like the way Wappler detects sub tables and give you a better database structure? We could perhaps hold a Poll to see how other users prefer how tables are shown in the Database Manager and in dropdowns. I think in dropdowns it is easier when tables that have direct relations to the already selected table come at top of the list since in most cases that are the tables you join with. All critics are welcome and will help us to decide which way we should go.

1 Like

Frankly it doesn’t make much difference. I know the database tables I want to use before I build a query. What would actually be really useful is a filter input wherever tables are listed - so I can quickly cut down the list of tables presented for selection in the join.

1 Like

Polls in the forum are skewed as hell. You should develop a simple polling mechanism in-app so all users are reachable.

2 Likes

This is actually a really good point. For beginner devs who are completely dependent on Wappler UI without knowing how the underlying thing works, the UI you guys have built with DB manager, and sub table and JSON support and similar other stuff is great.
But in most scenarios for us, the restrictions around table selection just does not make sense. This also ties into client side “add component” option restrictions.

The sub-tables setup is extremely helpful and really great, but because it does not open up options like a JOIN UI does, it becomes limiting. And with the main issue reported here, we have to do numerous work-arounds or just use custom queries. There was another similar issue reported around DISTINCT and sub-queries. We had to do so much more than 1 step, just because that was not working.

I don’t have any comments on DB manager since we don’t use it. But for query builder UI, a bit more flexible and complex UI wouldn’t hurt - for those who know how it works.

3 Likes

Bump.

Bump. Any update on this @patrick ?
Just the core issue… not the side-tracked discussion. :sweat_smile:

Hey @sid, not sure if this will help with your issues. I’ve noticed a UI issue where certain subtables do not show in menus in areas like the Database Manager or Sever Connect actions, but if I refresh schema on the parent table then I start seeing it again.