Multiple nested repeat data. Db queries

[cpuser]

Hi,

I am really stuck here. I’ve tried for several days now to try to resolve the issue I am having, but I am not getting anywhere and its brought my project to a halt.

I would like to have multiple nested category levels to be display from a database.

• MainSection
  – SubSection
  — Title
  ---- Item

I’ve followed the tutorial https://docs.wappler.io/t/nested-repeat-regions/5165
however it only does two levels and I need to have four levels.
I am stuck on getting a Third Level and a Fourth Level.

This is what I have server action. I can get level 1 (MainSection) and level 2 (SubSection), but I can’t get level 3 and level 4 to display? interestingly on the level 3 the “Div Repeat Children: rp_Title” on the App page doesn’t have a down arrow to expand and select the dynamic text. I tried hand coding it but it still didn’t work.

Any idea where I am going wrong?

[Teodor]

What do you see in your browser when you run the server action directly in the browser? Is all the nested data displayed?

[George]

Just as a tip for the server side you can use the new nested queries functionally to generate all the nested queries with just one action server side:

Using Nested Queries with the Database Query Builder

Intro You can access the nested data created with the help of sub tables using a single database query in your API Action. This way you don’t need complicated workflows such as using repeats and nesting database queries inside them to access nested data. Nested Queries First, create a new API Action: [Screenshot_28] And add a name to it: [Screenshot_29] Then add a new step inside it: [Screenshot_30] Select Database Query: [Screenshot_31] Open the Query Builder: [Screenshot_32] A…

[cpuser]

hi Teodor, yes, it appears when running the server action directly in the browser that all the data is display. It’s difficult to read because it’s not formatted for readability.

Also on the front end app page, I do note that DIV Repeat Children for Title doesn’t display the same options when selecting the Dynamic Expression select - and I had to just manually enter the code assuming it would be correct.

[cpuser]

hi George, are sub-tables properly supported? Like how would I be able to view and perhaps manage this in say phpMyAdmin if I ever have to? Would I have issues later on when I upload the site live and mysql DB tries to read subtables?
I wasn’t sure so that’s why I opted for the nested repeat regions tutorial.
Just wasn’t sure what the cleanest easiest way about this.

The above replay to @Teodore the code should work, but I am not sure where the issue is or if I missed something obvious.

[Teodor]

@cpuser

When adding code, please paste the code from your page, not a screenshot of it. It’s not really possible to copy and paste your code from the screenshot.

From what i see, the expressions are wrong. The nested repeat expression should just be dmx-bind:repeat="qr_items" and the data inside it <p>{{a32xSop_Item_text}}</p>

Probably you are just picking wrong data in the data picker.

[cpuser]

Hi Teodor,
Apologies about the code pasting. I understand that “Item” dynamic data might be wrong, but above it “Title” is also not displaying. I manage to only get two levels of nested repeats to work and not four.

Any ideas?
Is sub tables a better option? Just not sure what his method is not working?

[Teodor]

@cpuser

I understand that “Item” dynamic data might be wrong, but above it “Title” is also not displaying. I manage to only get two levels of nested repeats to work and not four.

Ok but i can’t really answer these questions without seeing your page/code.
Do you have a live link where i can check that?

[George]

@cpuser

are sub-tables properly supported? Like how would I be able to view and perhaps manage this in say phpMyAdmin if I ever have to?

Sub tables are just as any other tables with joins. They are just visually listed as nested in the database manager to show their hierarchy.

You can do nested queries with the query builder with any joins you want

[cpuser]

Hi @Teodor, I’ve inserted the code below for the front end page app:
I’ve also pasted the server connect further below

    <!doctype html>
    <html>
    <head>
    <base href="/usr/">
    <script src="../dmxAppConnect/dmxAppConnect.js"></script>
    <meta charset="UTF-8">
    <title>Untitled Document</title>

    <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.14.0/css/all.css" integrity="sha384-HzLeBuhoNPvSl5KYnjx0BT+WB0QEEqLprO+NBkkk5gbc67FTaL7XIGa2w1L0Xbgc" crossorigin="anonymous" />
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <link rel="stylesheet" href="../bootstrap/5/css/bootstrap.min.css" />
    <link rel="stylesheet" href="../css/style.css" />
    </head>
    <body is="dmx-app" id="a32x_sop">
    <dmx-serverconnect id="sc_list_sop" url="../dmxConnect/api/list_sop.php"></dmx-serverconnect>
    <section>
    <div class="container">

    <div class="row" is="dmx-repeat" id="rp_MainSection" dmx-bind:repeat="sc_list_sop.data.rp_mainsection">
        <h1>{{a32xSop_MainSection_id+' '+a32xSop_MainSection_text}}</h1>

        <div id="rp_SubSection" is="dmx-repeat" dmx-bind:repeat="qr_subsection">
            <h5>{{a32xSop_SubSection_id+' '+a32xSop_SubSection_text}}</h5>
               
                    
    <div id="rp_Title" is="dmx-repeat" dmx-bind:repeat="qr_title">
    <h5>{{a32xSop_Title_text}}Title</h5>

    <div id="rp_Item" is="dmx-repeat" dmx-bind:repeat="rp_subsection[0].rp_title[0].qr_item">
        
        <p>{{rp_title[0].qr_item[0].a32xSop_Item_text}}Item</p>
        
        </div>
    </div>

        </div>



    </div>

    </div>
    </section>
    <script src="../bootstrap/5/js/bootstrap.bundle.min.js"></script>
    </body>
    </html>

The server connect below:

    <?php
    require('../../dmxConnectLib/dmxConnect.php');


    $app = new \lib\App();

    $app->define(<<<'JSON'
    {
      "meta": {
        "$_GET": [
          {
            "type": "text",
            "name": "sort"
          },
          {
            "type": "text",
            "name": "dir"
          }
        ]
      },
      "exec": {
        "steps": [
          {
            "name": "qr_mainsection",
            "module": "dbconnector",
            "action": "select",
            "options": {
              "sql": {
                "type": "SELECT",
                "columns": [
                  {
                    "table": "a32xSop_MainSection",
                    "column": "*"
                  }
                ],
                "table": {
                  "name": "a32xSop_MainSection"
                },
                "primary": "a32xSop_MainSection_id",
                "joins": [],
                "query": "SELECT *\nFROM a32xSop_MainSection",
                "params": []
              },
              "connection": "a3sop"
            },
            "meta": [
              {
                "type": "number",
                "name": "a32xSop_MainSection_id"
              },
              {
                "type": "number",
                "name": "a32xSop_MainSection_order"
              },
              {
                "type": "text",
                "name": "a32xSop_MainSection_text"
              }
            ],
            "outputType": "array"
          },
          {
            "name": "rp_mainsection",
            "module": "core",
            "action": "repeat",
            "options": {
              "repeat": "{{qr_mainsection}}",
              "outputFields": [
                "a32xSop_MainSection_id",
                "a32xSop_MainSection_order",
                "a32xSop_MainSection_text"
              ],
              "exec": {
                "steps": [
                  {
                    "name": "qr_subsection",
                    "module": "dbconnector",
                    "action": "select",
                    "options": {
                      "sql": {
                        "type": "SELECT",
                        "columns": [
                          {
                            "table": "a32xSop_SubSection",
                            "column": "*"
                          }
                        ],
                        "table": {
                          "name": "a32xSop_SubSection"
                        },
                        "primary": "a32xSop_SubSection_id",
                        "joins": [],
                        "wheres": {
                          "condition": "AND",
                          "rules": [
                            {
                              "id": "a32xSop_SubSection.a32xSop_SubSection_MainSection_id",
                              "field": "a32xSop_SubSection.a32xSop_SubSection_MainSection_id",
                              "type": "double",
                              "operator": "equal",
                              "value": "{{a32xSop_MainSection_id}}",
                              "data": {
                                "table": "a32xSop_SubSection",
                                "column": "a32xSop_SubSection_MainSection_id",
                                "type": "number",
                                "columnObj": {
                                  "type": "integer",
                                  "name": "a32xSop_SubSection_MainSection_id"
                                }
                              },
                              "operation": "="
                            }
                          ],
                          "conditional": null,
                          "valid": true
                        },
                        "query": "SELECT *\nFROM a32xSop_SubSection\nWHERE a32xSop_SubSection_MainSection_id = :P1 /* {{a32xSop_MainSection_id}} */",
                        "params": [
                          {
                            "operator": "equal",
                            "type": "expression",
                            "name": ":P1",
                            "value": "{{a32xSop_MainSection_id}}"
                          }
                        ]
                      },
                      "connection": "a3sop"
                    },
                    "meta": [
                      {
                        "type": "number",
                        "name": "a32xSop_SubSection_id"
                      },
                      {
                        "type": "number",
                        "name": "a32xSop_SubSection_MainSection_id"
                      },
                      {
                        "type": "number",
                        "name": "a32xSop_SubSection_order"
                      },
                      {
                        "type": "text",
                        "name": "a32xSop_SubSection_text"
                      }
                    ],
                    "outputType": "array",
                    "output": true
                  },
                  {
                    "name": "rp_subsection",
                    "module": "core",
                    "action": "repeat",
                    "options": {
                      "repeat": "{{qr_subsection}}",
                      "outputFields": [
                        "a32xSop_SubSection_id",
                        "a32xSop_SubSection_order",
                        "a32xSop_SubSection_text"
                      ],
                      "exec": {
                        "steps": [
                          {
                            "name": "qr_title",
                            "module": "dbconnector",
                            "action": "select",
                            "options": {
                              "sql": {
                                "type": "SELECT",
                                "columns": [
                                  {
                                    "table": "a32xSop_Title",
                                    "column": "*"
                                  }
                                ],
                                "table": {
                                  "name": "a32xSop_Title"
                                },
                                "primary": "a32xSop_Title_id",
                                "joins": [],
                                "query": "SELECT *\nFROM a32xSop_Title\nWHERE a32xSop_SubSection_id = :P1 /* {{a32xSop_SubSection_id}} */",
                                "params": [
                                  {
                                    "operator": "equal",
                                    "type": "expression",
                                    "name": ":P1",
                                    "value": "{{a32xSop_SubSection_id}}"
                                  }
                                ],
                                "wheres": {
                                  "condition": "AND",
                                  "rules": [
                                    {
                                      "id": "a32xSop_Title.a32xSop_SubSection_id",
                                      "field": "a32xSop_Title.a32xSop_SubSection_id",
                                      "type": "double",
                                      "operator": "equal",
                                      "value": "{{a32xSop_SubSection_id}}",
                                      "data": {
                                        "table": "a32xSop_Title",
                                        "column": "a32xSop_SubSection_id",
                                        "type": "number",
                                        "columnObj": {
                                          "type": "integer",
                                          "nullable": true,
                                          "name": "a32xSop_SubSection_id"
                                        }
                                      },
                                      "operation": "="
                                    }
                                  ],
                                  "conditional": null,
                                  "valid": true
                                }
                              },
                              "connection": "a3sop"
                            },
                            "meta": [
                              {
                                "type": "number",
                                "name": "a32xSop_Title_id"
                              },
                              {
                                "type": "number",
                                "name": "a32xSop_SubSection_id"
                              },
                              {
                                "type": "number",
                                "name": "a32xSop_Title_order"
                              },
                              {
                                "type": "text",
                                "name": "a32xSop_Title_text"
                              }
                            ],
                            "outputType": "array",
                            "output": true
                          },
                          {
                            "name": "rp_title",
                            "module": "core",
                            "action": "repeat",
                            "options": {
                              "repeat": "{{qr_title}}",
                              "outputFields": [
                                "a32xSop_Title_id",
                                "a32xSop_Title_order",
                                "a32xSop_Title_text",
                                "a32xSop_SubSection_id"
                              ],
                              "exec": {
                                "steps": {
                                  "name": "qr_item",
                                  "module": "dbconnector",
                                  "action": "select",
                                  "options": {
                                    "sql": {
                                      "type": "SELECT",
                                      "columns": [
                                        {
                                          "table": "a32xSop_Item",
                                          "column": "*"
                                        }
                                      ],
                                      "table": {
                                        "name": "a32xSop_Item"
                                      },
                                      "primary": "a32xSop_Item_id",
                                      "joins": [],
                                      "wheres": {
                                        "condition": "AND",
                                        "rules": [
                                          {
                                            "id": "a32xSop_Item.a32xSop_Item_Title_id",
                                            "field": "a32xSop_Item.a32xSop_Item_Title_id",
                                            "type": "double",
                                            "operator": "equal",
                                            "value": "{{a32xSop_Title_id}}",
                                            "data": {
                                              "table": "a32xSop_Item",
                                              "column": "a32xSop_Item_Title_id",
                                              "type": "number",
                                              "columnObj": {
                                                "type": "integer",
                                                "nullable": true,
                                                "name": "a32xSop_Item_Title_id"
                                              }
                                            },
                                            "operation": "="
                                          }
                                        ],
                                        "conditional": null,
                                        "valid": true
                                      },
                                      "query": "SELECT *\nFROM a32xSop_Item\nWHERE a32xSop_Item_Title_id = :P1 /* {{a32xSop_Title_id}} */",
                                      "params": [
                                        {
                                          "operator": "equal",
                                          "type": "expression",
                                          "name": ":P1",
                                          "value": "{{a32xSop_Title_id}}"
                                        }
                                      ]
                                    },
                                    "connection": "a3sop"
                                  },
                                  "meta": [
                                    {
                                      "type": "number",
                                      "name": "a32xSop_Item_id"
                                    },
                                    {
                                      "type": "number",
                                      "name": "a32xSop_Item_Title_id"
                                    },
                                    {
                                      "type": "text",
                                      "name": "a32xSop_Item_order"
                                    },
                                    {
                                      "type": "text",
                                      "name": "a32xSop_Item_textPrefix"
                                    },
                                    {
                                      "type": "text",
                                      "name": "a32xSop_Item_text"
                                    },
                                    {
                                      "type": "text",
                                      "name": "a32xSop_Item_action"
                                    },
                                    {
                                      "type": "text",
                                      "name": "a32xSop_Item_notes1"
                                    },
                                    {
                                      "type": "text",
                                      "name": "a32xSop_Item_notes2"
                                    }
                                  ],
                                  "outputType": "array",
                                  "output": true
                                }
                              }
                            },
                            "output": true,
                            "meta": [
                              {
                                "name": "$index",
                                "type": "number"
                              },
                              {
                                "name": "$number",
                                "type": "number"
                              },
                              {
                                "name": "$name",
                                "type": "text"
                              },
                              {
                                "name": "$value",
                                "type": "object"
                              },
                              {
                                "name": "a32xSop_Title_id",
                                "type": "number"
                              },
                              {
                                "name": "a32xSop_SubSection_id",
                                "type": "number"
                              },
                              {
                                "name": "a32xSop_Title_order",
                                "type": "number"
                              },
                              {
                                "name": "a32xSop_Title_text",
                                "type": "text"
                              },
                              {
                                "name": "qr_item",
                                "type": "array",
                                "sub": [
                                  {
                                    "type": "number",
                                    "name": "a32xSop_Item_id"
                                  },
                                  {
                                    "type": "number",
                                    "name": "a32xSop_Item_Title_id"
                                  },
                                  {
                                    "type": "text",
                                    "name": "a32xSop_Item_order"
                                  },
                                  {
                                    "type": "text",
                                    "name": "a32xSop_Item_textPrefix"
                                  },
                                  {
                                    "type": "text",
                                    "name": "a32xSop_Item_text"
                                  },
                                  {
                                    "type": "text",
                                    "name": "a32xSop_Item_action"
                                  },
                                  {
                                    "type": "text",
                                    "name": "a32xSop_Item_notes1"
                                  },
                                  {
                                    "type": "text",
                                    "name": "a32xSop_Item_notes2"
                                  }
                                ]
                              }
                            ],
                            "outputType": "array"
                          }
                        ]
                      }
                    },
                    "output": true,
                    "meta": [
                      {
                        "name": "$index",
                        "type": "number"
                      },
                      {
                        "name": "$number",
                        "type": "number"
                      },
                      {
                        "name": "$name",
                        "type": "text"
                      },
                      {
                        "name": "$value",
                        "type": "object"
                      },
                      {
                        "name": "a32xSop_SubSection_id",
                        "type": "number"
                      },
                      {
                        "name": "a32xSop_SubSection_MainSection_id",
                        "type": "number"
                      },
                      {
                        "name": "a32xSop_SubSection_order",
                        "type": "number"
                      },
                      {
                        "name": "a32xSop_SubSection_text",
                        "type": "text"
                      },
                      {
                        "name": "qr_title",
                        "type": "array",
                        "sub": [
                          {
                            "type": "number",
                            "name": "a32xSop_Title_id"
                          },
                          {
                            "type": "number",
                            "name": "a32xSop_SubSection_id"
                          },
                          {
                            "type": "number",
                            "name": "a32xSop_Title_order"
                          },
                          {
                            "type": "text",
                            "name": "a32xSop_Title_text"
                          }
                        ]
                      },
                      {
                        "name": "rp_title",
                        "type": "array",
                        "sub": [
                          {
                            "name": "a32xSop_Title_id",
                            "type": "number"
                          },
                          {
                            "name": "a32xSop_SubSection_id",
                            "type": "number"
                          },
                          {
                            "name": "a32xSop_Title_order",
                            "type": "number"
                          },
                          {
                            "name": "a32xSop_Title_text",
                            "type": "text"
                          },
                          {
                            "name": "qr_item",
                            "type": "array",
                            "sub": [
                              {
                                "type": "number",
                                "name": "a32xSop_Item_id"
                              },
                              {
                                "type": "number",
                                "name": "a32xSop_Item_Title_id"
                              },
                              {
                                "type": "text",
                                "name": "a32xSop_Item_order"
                              },
                              {
                                "type": "text",
                                "name": "a32xSop_Item_textPrefix"
                              },
                              {
                                "type": "text",
                                "name": "a32xSop_Item_text"
                              },
                              {
                                "type": "text",
                                "name": "a32xSop_Item_action"
                              },
                              {
                                "type": "text",
                                "name": "a32xSop_Item_notes1"
                              },
                              {
                                "type": "text",
                                "name": "a32xSop_Item_notes2"
                              }
                            ]
                          }
                        ]
                      }
                    ],
                    "outputType": "array"
                  }
                ]
              }
            },
            "output": true,
            "meta": [
              {
                "name": "$index",
                "type": "number"
              },
              {
                "name": "$number",
                "type": "number"
              },
              {
                "name": "$name",
                "type": "text"
              },
              {
                "name": "$value",
                "type": "object"
              },
              {
                "name": "a32xSop_MainSection_id",
                "type": "number"
              },
              {
                "name": "a32xSop_MainSection_order",
                "type": "number"
              },
              {
                "name": "a32xSop_MainSection_text",
                "type": "text"
              },
              {
                "name": "qr_subsection",
                "type": "array",
                "sub": [
                  {
                    "type": "number",
                    "name": "a32xSop_SubSection_id"
                  },
                  {
                    "type": "number",
                    "name": "a32xSop_SubSection_MainSection_id"
                  },
                  {
                    "type": "number",
                    "name": "a32xSop_SubSection_order"
                  },
                  {
                    "type": "text",
                    "name": "a32xSop_SubSection_text"
                  }
                ]
              },
              {
                "name": "rp_subsection",
                "type": "array",
                "sub": [
                  {
                    "name": "a32xSop_SubSection_id",
                    "type": "number"
                  },
                  {
                    "name": "a32xSop_SubSection_MainSection_id",
                    "type": "number"
                  },
                  {
                    "name": "a32xSop_SubSection_order",
                    "type": "number"
                  },
                  {
                    "name": "a32xSop_SubSection_text",
                    "type": "text"
                  },
                  {
                    "name": "qr_title",
                    "type": "array",
                    "sub": [
                      {
                        "type": "number",
                        "name": "a32xSop_Title_id"
                      },
                      {
                        "type": "number",
                        "name": "a32xSop_SubSection_id"
                      },
                      {
                        "type": "number",
                        "name": "a32xSop_Title_order"
                      },
                      {
                        "type": "text",
                        "name": "a32xSop_Title_text"
                      }
                    ]
                  },
                  {
                    "name": "rp_title",
                    "type": "array",
                    "sub": [
                      {
                        "name": "a32xSop_Title_id",
                        "type": "number"
                      },
                      {
                        "name": "a32xSop_SubSection_id",
                        "type": "number"
                      },
                      {
                        "name": "a32xSop_Title_order",
                        "type": "number"
                      },
                      {
                        "name": "a32xSop_Title_text",
                        "type": "text"
                      },
                      {
                        "name": "qr_item",
                        "type": "array",
                        "sub": [
                          {
                            "type": "number",
                            "name": "a32xSop_Item_id"
                          },
                          {
                            "type": "number",
                            "name": "a32xSop_Item_Title_id"
                          },
                          {
                            "type": "text",
                            "name": "a32xSop_Item_order"
                          },
                          {
                            "type": "text",
                            "name": "a32xSop_Item_textPrefix"
                          },
                          {
                            "type": "text",
                            "name": "a32xSop_Item_text"
                          },
                          {
                            "type": "text",
                            "name": "a32xSop_Item_action"
                          },
                          {
                            "type": "text",
                            "name": "a32xSop_Item_notes1"
                          },
                          {
                            "type": "text",
                            "name": "a32xSop_Item_notes2"
                          }
                        ]
                      }
                    ]
                  }
                ]
              }
            ],
            "outputType": "array"
          }
        ]
      }
    }
    JSON
    );
    ?>

[cpuser]

Thanks @George I am on a PHP server model, so currently sub tables are not supported for php server model?

[Teodor]

So isn’t this code working for you?

<div class="row" is="dmx-repeat" id="rp_MainSection" dmx-bind:repeat="sc_list_sop.data.rp_mainsection">
<h1>{{a32xSop_MainSection_id+' '+a32xSop_MainSection_text}}</h1>
    <div id="rp_SubSection" is="dmx-repeat" dmx-bind:repeat="qr_subsection">
        <h5>{{a32xSop_SubSection_id+' '+a32xSop_SubSection_text}}</h5>
        <div id="rp_Title" is="dmx-repeat" dmx-bind:repeat="qr_title">
            <h5>{{a32xSop_Title_text}}</h5>
            <div id="rp_Item" is="dmx-repeat" dmx-bind:repeat="qr_item">
                <p>a32xSop_Item_text}}</p>
            </div>
        </div>
    </div>
</div>

@cpuser

I am on a PHP server model, so currently sub tables are not supported for php server model?

PHP is supported as well.

[cpuser]

No it is not working for me. What I was saying before is that ‘Data Bindings’ do not display the data to be bound until the Div rp_Title and Div rp_Item, like how it is for the SubSection and MainSection. So they are not repeating in the same way the high level categories are repeating.
Can you see any cause for that? I have checked my queries in the Server side app and they are working.

[Teodor]

Can you send a link to your page please, so that i can test it?

[cpuser]

I haven’t setup a live server as yet. Is there another way? Or I can try to quickly setup a live server.

[Teodor]

Well you can use something like https://ngrok.com/ to open your local server to the public.

[cpuser]

I’ve just created and uploaded onto live server. I have direct messages you the access details.

[Teodor]

So your expressions are just wrong … when you add a repeat step in the server action, just disable output for the query it is repeating and use the repeater as a source for the repeat region on the page. This way you won’t see the queries also outputted on the page, confusing you what to pick. That is explained in the docs.

So you need to use these expressions for your repeat …

<div class="row" is="dmx-repeat" id="rp_MainSection" dmx-bind:repeat="sc_list_sop.data.rp_mainsection">
<h1>{{a32xSop_MainSection_id+' '+a32xSop_MainSection_text}}</h1>
    <div id="rp_SubSection" is="dmx-repeat" dmx-bind:repeat="rp_subsection">
        <h5>{{a32xSop_SubSection_id+' '+a32xSop_SubSection_text}}</h5>
        <div id="rp_Title" is="dmx-repeat" dmx-bind:repeat="rp_title">
            <h5>{{a32xSop_Title_text}}</h5>
            <div id="rp_Item" is="dmx-repeat" dmx-bind:repeat="qr_item">
                <p>a32xSop_Item_text}}</p>
            </div>
        </div>
    </div>
</div>

[cpuser]

Sorry, just to clarify. Do I deselection Output for the Database Queries in the Server app?

[Teodor]

Turn off output only for the queries which you are repeating in the server action, i.e. these 3:

Then update your code as in my last post, so it uses the repeats for the repeat children expressions … that is the whole point of making these repeats nested - so you are using them, not the queries they are repeating!