How to properly use Data Detail from a table within a repeat region (help!)

[jellederijke]

Hi there fellow wapplers,

I’m stuck with this one issue I can’t seem to get my head around. It would be great if someone could point me in the right direction

Please note that this quite a LONG story, my apologies.

I’m trying to get a Data Detail in a modal to work for records in a row of a table that itself is within a repeat on a page.

Data setup
Let me try to explain.
We are talking about these different datatypes:
-users
-gemeentes
Each user can have many gemeentes.
Each gemeente can have many users.
So for this to work I have these tables in my db:

• Users (gebruikers in my language)

• Gemeentes

• Users_Gemeentes (with just a gemeente_id and user_id_

What I am trying to achieve is three things:

1. the logged in user can view all the users per gemeente that he himself belongs to (this works)
2. this user can view/edit details of any user from a gemeente that he belongs to
3. this user can delete access to a gemeente from any user within a gemeente that he belongs to

So #2 and #3 currently don’t work as expected.

Server action with Queries to get #1 working
This is the basic server action:

The first Query gets the current user id (in this case 3784, this will change to securityid on the actual application) and left joins the gemeentes table to check which gemeentes he belongs to:

After that I have another query withing a repeat to retrieve every user for each gemeente that this user belongs to:

This outputs the correct data, so I am fairly happy with that.

This enabled me to solve part 1: showing all the users per gemeente that this user belongs to
Screenshot of the tables:

Working with this code:

<div class="row row-cols-1" is="dmx-repeat" id="repeat1" dmx-bind:repeat="sc_current_users_gemeentes_users.data.repeat">
<div class="col">
<div class="card">
  <div class="card-body">
    <h1>{{Naam_bedrijf}}</h1>
    <div class="table-responsive">
        <table class="table table-striped table-bordered table-hover">
            <thead>
                <tr>
                    <th class="w-25">Voornaam</th>
                    <th class="w-25">Achternaam</th>
                    <th class="w-25">Email</th>
                    <th class="w-10">Laatst ingelogd</th>
                    <th class="w-10">Bewerken</th>
                    <th class="w-10">Toegang verwijderen</th>
                    
                </tr>
            </thead>
            </table>
            </div>
    <div dmx-repeat:repeat2="get_gemeentes_users">
    <div class="table-responsive">
<table class="table table-bordered table-hover">
    <tbody>
        <tr>
            <td class="w-25">{{Voornaam}}</td>
            <td class="w-25">{{Achternaam}}</td>
            <td class="w-25">{{email}}</td>
            <td class="w-10">{{most_recent_login.formatDate('dd-MM-yyyy')}}</td>
            <td class="w-10"><button id="btnaanpassen" class="btn btn-primary" dmx-on:click="modalgebruikeraanpassen.show();modalgebruikeraanpassen.data_detail1.select(gebruiker_id_2)">Bewerken</button></td>
            <td class="w-10">
<button id="btnverwijderen" class="btn btn-danger" dmx-on:click="modalgebruikertoegangverwijderen.show();modalgebruikertoegangverwijderen.data_detail2.select(gebruiker_id_2)">Verwijderen</button>
</td>
        </tr>
    </tbody>
</table>
</div>

</div>

  </div>
</div>

</div>
</div>

So the first query in the server action displays the Gemeentes in a card body and I have hacked-and-slashed together a table to display the server action repeat within the repeat on the page. Not super happy with how to table can’t seem to keep it’s responsiveness, hence the w-25 classes.

So this works fairly well.

The problem
So I need the orange button the give the details of that specific user in a Data Detail in a modal and be able to edit some fields and save the record. (this is #2).

And I need the red button (#3) to be able to first show the data detail in the modal and on confirmation delete the row with the right user_id and gemeente_id so we can remove access to this gemeente and not delete the user all together.

However, I can only seem to get the data detail to properly show details from the first gemeente, so the first repeat.

When I click on the orange button for a user in the second table the data detail remains empty.

Except when I click my own user account, somehow that does give the details.

I think it has something to do with the repeat[0] that is bind to the data detail:

<div class="modal-dialog modal-dialog-centered" role="document">
    <div class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title">Gebruiker aanpassen</h5>
        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body">
<div class="container pl-0 pr-0" is="dmx-data-detail" id="data_detail1" dmx-bind:data="sc_current_users_gemeentes_users.data.repeat[0].get_gemeentes_users" key="gebruiker_id_2">
<form id="form1" is="dmx-serverconnect-form" method="post" action="dmxConnect/api/front_end/gemeente/edit_user.php" dmx-on:success="sc_current_users_gemeentes_users.load({},true)" dmx-on:submit="modalgebruikeraanpassen.hide()">
<div class="form-group row" id="voornaam">
<div class="col-3">
    <label for="input1" class="col-form-label">Voornaam</label>
    </div>
  <div class="col-9">
    <input id="text2" name="voornaam" type="text" class="form-control" dmx-bind:value="data_detail1.data.Voornaam">
  </div>
</div>
<div class="form-group row" id="achternaam">
<div class="col-3">
    <label for="input1" class="col-form-label">Achternaam</label>
    </div>
  <div class="col-9">
    <input id="text3" name="achternaam" type="text" class="form-control" dmx-bind:value="data_detail1.data.Achternaam">
  </div>
</div><div class="form-group row" id="emailadres">
<div class="col-3">
    <label for="input1" class="col-form-label">E-mailadres</label>
    </div>
  <div class="col-9">
    <input id="email" name="email" type="text" class="form-control" dmx-bind:value="data_detail1.data.email">
    <input id="gebruikerid" name="gebruikerid" type="hidden" class="form-control" readonly="true" dmx-bind:value="data_detail1.data.gebruiker_id_2">
  </div>
</div>

<button type="submit" class="btn btn-primary">Save changes</button></form>
</div>
      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
        
      </div>
    </div>
  </div>

When I change repeat[0] to repeat[1] I can only see the details for the second table. Which is cool, but no fix.

So what I probably need is either a way to make this repeat[0] dynamic for each repeat, or perhaps a different setup with the queries and or repeats. I just can’t seem to figure out what!

Any any any help is greatly appreciated and as per usual I am happy to share the full solution once I get it to work.

Thanks for the support.

bg

Jelle

[sid]

Hi.
For nested data, I would recommend to not use Data Detail component.
Instead, just use regular server connect, and use where condition formatter to filter it down to the nested value you are after.

1. On click of the nested table’s edit/view button, set the current parent & nested ids in two separate variable.
2. Create a third variable which will be used for final data.
3. On shown event of the modal, set the value of the third variable using sc.data.where(...)[0].
4. Now, you can use this third variable as data source for binding values in the edit form.
5. You will loose the option to use picker with the data in third variable, but I don’t think it would matter much, since forms usually don’t require too many frequent changes in bindings.

Hope this makes sense.

You could try to work with data details by settings its source to a similar where formatter server connect, but I am not sure how well that works.

[jellederijke]

Hi Sid,

Thanks for your mega swift reply! I was actually just testing to see if I can get the right user_id and gemeente_id to show up in modal through using variables. This works!

So your step 1 works for me.

I do not yet understand how you would use a regular server connect in this scenario. Do you mean creating a new server action, and use GET variables to filter the queries? I am not totally sure what you mean.

I also do not fully understand what you mean with the third variable.

sc.data.where(...)[0]

Do you mean I can just do a where statement in the code where I call the sc? How would one format this? I am not yet familiar with this method.

Your number 5 does not really sound like a problem, if this means I need to bind the data in the code instead of the data picker.

Thanks so much again for the quick response.

[jellederijke]

So I have now successfully managed to show the details of a user from any one of the repeats (gemeentes) in a modal. I did this by using the user_id as a GET variable on a get_single_user query.

This is good!

Summary of previous security issue

However, I am a bit concerned that an unfriendly user can now access user details outside of his own gemeentes by changing the query params:

I can just change this value into 3785 and get the details of a user that is not necessarily within my gemeente.

Perhaps this is not the direction you meant at all. Or I should build a query with where conditions to check if the requested user in the GET var is within the gemeentes of the current logged in user.

EDIT:

This security issue here I fixed by doing the same joins to check if the requested user is within the logged in users gemeente. So this is no longer an issue

Thanks SID, your response has ignited a new thought pattern that might just lead to the end-solution!

[jellederijke]

So I have now fixed this issue and everything is working perfectly! Oddly enough I haven’t used any app connect variables at all, but I opted for a new server with GET-variables.

If anyone is interested this is my current setup (obviously open for suggestions for improvement):

I’ve created a new server action called get_single_user_securely. This server action takes 2 get vars userid and gemeenteid. These variables serve as a condition in the queries. I prevent malicious attempts to access user details from gemeetes outside of the currently logged in user by checking this as a condition in my join tables:

I then get the user details with a repeat and this query:

Now I have just added this new server connect to the page and load the sc on button click with the 2 get params and bind the form fields to the get_single_user server connect. Et voila it works!

The edit single user and delete user access are obviously different server connect files, but they are no different than a regular post form server connect setup.

So @sid thanks again for giving me a nudge and please do let me know if this solution makes zero sense to you.

bg

Jelle

[sid]

Glad to see you were able to make this work.

The solution you are using is something that I would have implemented in the past too.
In this solution, if am not wrong, you are calling a server action just to get the data that you already have in another server connect.
With the variables and where formatter logic, you could get desired information on the client side from the original server connect itself, without hitting the servers again.

Maybe it will make sense in future once you use Wappler more, and learn more.

[jellederijke]

Thanks for this intel, Sid. I did notice that requesting the data again takes another 400 to 500ms, so that is definitely not great. And seems unnecessary.

I’d love to use the variables and where formatter logic. Are there any docs or topics that you know of that get into this technique?

[sid]

@jellederijke

Are there any docs or topics that you know of that get into this technique?

I am really bad at sharing Wappler docs. I could not find an example usage of where formatter.

[jellederijke]

Ok thanks for the help regardless, Sid. I’m super grateful. Have a nice day!

[karh]

Hey Sid, I’m running into this exact same issue and am applying your solution.

Implemented each step - but also stuck on the ‘where’ step.

I figured this would be what you meant:
{{teacher_get_school_courses.data.query_courses_in_this_school.where($key, 1, '==')[0])}}

(Using a static value for ‘1’ now, will be replaced with the variable you mentioned)
This is the data seen from the network tab:

But I get the following error in console:
dmxAppConnect.js:7 Formatter where in expression [teacher_get_school_courses.data.query_courses_in_this_school.where($key, 1, '==')[0])] doesn't exist for type array

My goal is to get this array of data:
{{teacher_get_school_courses.data.query_courses_in_this_school[0].get_course_chapters_n_lectures.get_course_chapters[0].get_lectures_per_chapter}}

Where the two indexes that are [0] will be replaced with the two variables that I created according to your instructions.

Can you help me out getting the where to work please?
Thanks!

[sid]

@karh

doesn’t exist for type array

This just means your page is missing Wappler’s formatter JS file. Check if layout file needs saving.
Or if you have typed the code, select any random formatter from picker UI just to include Wappler library file.

[karh]

Didn’t notice, had to save it yes.

However the errors are still occuring…

Althought I can literally see it’s being loaded

Any ideas?

[sid]

This is new error.
You are applying toNumber to a value which is already a number, as the error states. Just remove the formatter.

[Teodor]

You can’t apply toNumber formatter to a number

[karh]

Another facepalm, I just realised that lol.

So back to the original question, the dmxFormatter.js is loaded.

I’m still getting the same error

I’m guessing my syntax is wrong. Can you give a more detailled example of your sc.data.where(...)[0] example? @sid

I’ve tried so many things now, my last one is:

teacher_get_school_courses.data.where(`id`, 1, '==')

[sid]

Use the UI to make the expression.
What you have looks ok so far.

Also, try using dmx.parse in console to test you expression.

[karh]

Thanks - I almost got it… just confused with the ‘sub’ .where…
Now I have this testing html:

<dmx-value id="test_final_data" dmx-bind:value="teacher_get_school_courses.data.query_courses_in_this_school.where(`id`, 1, '==')[0]"></dmx-value>
test_final_data.value (expected: object Object): {{test_final_data.value}}
<br>
test_final_data.value.course_id: {{test_final_data.value.course_id}}

Outputs this on page:

That’s perfect, but it’s only “one drill down”.

Now I want to apply another .where on it.
So this is the static version of my whole databinding:

teacher_get_school_courses.data.query_courses_in_this_school[0].get_course_chapters_n_lectures.get_course_chapters[0].get_lectures_per_chapter

I have this part dynamic with the .where:

teacher_get_school_courses.data.query_courses_in_this_school[0]

Now how do I put the result of the first .where into another .where so I can get the second part dynamic:

.get_course_chapters_n_lectures.get_course_chapters[0]

[karh]

Solution for multiple .where filters:

Got it working, this is my end formatter:
((teacher_get_school_courses.data.query_courses_in_this_school.where(id, datapick_course_id.value, '==')[0]).get_course_chapters_n_lectures.get_course_chapters.where(id, datapick_chapter_id.value, '==')[0]).get_lectures_per_chapter.where(id, datapick_lecture_id.value, '==')[0]

You basically get a normal object again after the .where formatter. So you drill down and apply more .wheres

[sid]

That is correct. Great that you figured it out.

I don’t know what the data structure is and in what context do you need these three where formatters, but see if you can move some things to server side. The reason why I say this bindings keep evaluating themselves on the page. So you need to be mindful as too many bindings can degrade page performance… especially bindings with formatters.
You can either move the heavy lifting on server side.
Or, evaluate this only on say a button click event or a SC success event. That way, the evaluation happens only once, and you have the final value available for further use via a variable.

[jellederijke]

This is great stuff, guys! I will try this out, as well.
Sid I am amazed by your commitment to thid community and your in depth answers. Thanks!