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.
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:
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.
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.
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.
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.
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.).
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.
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.
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.
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.
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.
