I have an existing database with table names such as
Customer and Invoice. PHP/MySQL
I have already developed the system based on this database (amongst other tables).
For me to use nested repeats would I have to rename my tables and manage it through Database manager? I mainly use Navicat to manager my db.
I won’t be able to rename my tables. Too much has already been developed.
Also, which would be the more suitable tutor for Nested Repeats?
Or
If your sub tables are correctly linked with foreign keys but just don’t have the prefix of the parent name, is not really a problem.
They will just show in the database in the main level, but in the query builder then will show as sub tables when you start to query the main table as there both direct sub tables as well references are shown.
So you should be able to make nested queries as well. Just make sure your database schema is fully refreshed first in tge database manager.
Our tables don't have the parent table's prefix, just foreign keys - any chance subtables would work there?
yes that is what I said above
Oh right! Mis-read. Thanks.
In MySQL is it possible to assign a foreign key to a field even if it’s not the same name as the field in the other table it’s linking to? That’s my issue, I don’t have the name right.
Just answered myself. Yes it’s possible as the link is created in the dab and you select which field the foreign key is assigned to. Just because it has the same field name doesn’t make it a foreign key.
@George on my existing DB I have set a foreign key on one of the fields to its related key on the main table but the table doesn’t appear as a Subtable when I open up query builder? It just lists it as a normal
Table.
Any ideas what the issue it?
It could be how the tables are named seen as the tables was existing, is the table prefixed with the parent tables name?
As per @George it’s not necessary to have then prefix with the parent name - hence my question to George as why it wouldn’t be appearing?
I have linked the tables with the foreign key on the desired field.
What would the issue be @George?
What I was saying is I think the tables are still usable without a prefix but to see them as nested a prefix might be needed they still show as you said but like a normal table so once you choose you main table in the query the table with the reference will also be there but green at the bottom
But I maybe wrong
Sorry it’s correct. They tables will still work but without the proper naming convention they won’t appear nested in the db manager
So in Query manager (not database manager) will the table behave like subtables when it has the foreign id?
I guess the whole point of asking is I want to be able to use Nested Repeats and from the docs there are two ways, like in my original post- will I be able to use nested repeats when I have foreign ID without table prefix? And how would it work in Query manager (not database manager).
I struggle with nested repeats and I don’t know what the best way to get them to work.
Hi @cpuser,
Yes, the subtables will appear in the query builder if you have set up a foreign key between two tables.
The only difference between two tables - main & subtable setup with a foreign key - using naming convention with prefixes and without prefixes is how these are displayed in the Database manager. If the tables have the same / matching prefixes, then these will appear in the Main -> Subtable hierarchy in the database manager, otherwise these will appear as two Main tables. See the screen shot below showing the main -> subtable hierarchy with user_id as a Reference field.
When using the main / subtable structure in the Query Builder, there's no need to add another step to run nested repeats anymore if using main / subtable structure with FK. As both tables will appear in the same query, you can use fields from both tables on the client side. See an example below with a Repeat on Main table query and AC component Form Repeat to access fields from the subtable details.
<div dmx-repeat:repeat1="sc_clients_list.data.q_clients_list">
Client: {{email}}
<div is="dmx-form-repeat" id="formRepeat1" dmx-bind:items="details">
Name: {{details[0].first_name}} {{details[0].last_name}}
</div>
</div>
@guptast this is great thanks. The last part is the part I was asking about. But if I have a nested repeat and the second table records how will they be repeated nested inside the main table display on the page. I understand I can see the records within the one query (ibis a join I take it)?
Just not sure which tutorial to follow to achieve nested repeats. I want to be able to display all the related records from the second table within the main list,
For example:
Project 1
related media record
related media record
related media record …. Etc
Project 2
related media record
related media record
related media record …. Etc
Like a normal nested repeat.
I was told it’s easier with subtables. If I have the query with a join in the query manager (with the foreign key setup) then how do I display the nested records on the user’s page.
If you have a main -> subtable structure, then you can use one query to fetch data from two or more tables instead of running nested repeat steps. These are Joins in the background and Wappler’s code is creating these joins based on the FK to create a single query. I have included a couple of screenshots - Query Builder window showing two tables and output of the code I included in my previous reply.
Thanks again for replying and helping me out @guptast. A couple of questions:
My sub table does appear inside like yours does in orange (that’s because I don’t use Database manager) and I have manually linked the tables via foreign key outside Wappler database manager.
So in my query builder they appear as normal tables and I would have to add them both.
For each record that I have in the main table, there are many related records in the sub table.
Each Project has many media and I want to list all the projects and there related media.
So I have to use a nested repeat don’t I? How will it know that my subtable needs to repeat to display all the related records from the subtable?
To get multiple repeat records from the subtable, you can use Repeat Children component inside the main repeat. Here’s an example code with the output, for each user_id, showing multiple details if available:
<div dmx-repeat:repeat1="sc_clients_list.data.q_clients_list">
<div class="card card-default m-2 p-2">
<strong>
Client: {{email}} (From main table)
</strong>
<div id="repeat2" is="dmx-repeat" dmx-bind:repeat="details" key="detail_id" class="mx-2 py-2">
<p class="mb-0 py-1">
Name: {{first_name}} {{last_name}}
<br>
Role: {{role}} || Country: {{country}} || Last Login: {{most_recent_login.formatDate('dd MMM yyyy HH:mma')}}
</p>
</div>
</div>
</div>
Hi @guptast, thanks for explaining
I’m having real trouble. Firstly I don’t see those subtables in orange. I do have the foreign keys setup int he DB> I also tried your code and couldn’t get it to work.
So I tried the tutorial (https://docs.wappler.io/t/nested-repeat-regions/5165), yet in the repeat2 on the App page, my secondary query does not appear as an option. here is what I have
The subtables appear in orange in the Query Builder Window. Can you please attach a screenshot of the Database Query Builder Window listProjects?
