A better way to edit/replace an on many to many post?

Kevin Wasie

I am hoping to find a better way to update/replace/delete records on a many to many relationship POST in the api...

There are 3 tables:
• user: • id
• name
• email
• property:• id
• name
• address
• users_to_properties• id
• user_id: table reference
• property_id: table reference
• property_user_role: enum
• user_last_accessed: time
• user_has_access: bool - instead of deleting the record, this determines if user has access to this property still


When we make an update/edit to a property record, our UI sends a whole new array of what needs updated in the users_to_properties table that looks like this:
property = {
id: 1,
name: "foo",
email: "foo",
input_property_users{
[
50,
8,
10
]
}
}
So, when the UI sends a whole new record, the POST property endpoint needs to complete these requirements:
1. If the user exists in the input, but not in the database, then add the user to the users_to_properties table with property_id/property_user_role/etc set
2. if the user exists in database, but not in the input, then set user_has_access to false for the correct property_id/user_id
3. If the user exists in the database, and the input, then update the user_last_acessed to be now() and user_has_access = true


**Is there a simple way to complete this?**

I am able to complete this logic with a very ugly set of for each looks that looks something like this:
matched_record = false;

for each user in input_property_users{
query all records from db.users_to_properties where (users_to_properties.user_id = user && users_to_properties.property_id = property.id )= matched_record;
if matched_record = true{
edit users_to_properties table and set records // this completes #3 requirement above
}
else {
add user to users_to_properties with correct parameters // this completes #1 requirement above
}
Now, to complete #2 requirement above, we need another for loop

db_user_to_properties = all records in users_to_properties where users_to_properties.property_id = property.id

for each db_user in db_user_to_properties{
matched_record = false
for each user in input_property_users{
if user = db_user.user_id {
matched_record = true
}
}
if matched_record != true
{do the work here to update db record
// this completes requirement # 2 above}
}This method works, but it is ugly and takes a lot of resources, and a pain to maintain/debug.

I know there has to be a better solution. Can you please assist? 

Thank you in advance.











 

Michael Udinski

 have you checked out the Add or Edit Record function yet? It basically performs the logic... if field value exists in DB then update it, if not found then add the record... there might be some more nuance in what you're trying to accomplish but I think that's a good place to start to look at

Kevin Wasie

 thanks for pointing that out. I'll look into it...

To make this simpler, here are some diagrams. Basically, I think what I am trying to do is  outer joins(?) and then an inner join between the int[] and property_to_user db table. Not sure if that is the right terminology. I have no idea how to do that in Xano. 

Once I have the data loaded into variables, I can figure out the rest. I just dont know how to get it into variables without these crazy for loops that I have right now.
[Screen Shot 2022-04-12 at 9.16.12 PM.png][Screen Shot 2022-04-12 at 9.13.52 PM.png][Screen Shot 2022-04-12 at 9.12.01 PM.png]

Kevin Wasie

Ah.  Unfortunately, the Add or Edit function will not work.

I need to compare on multiple parameters (user_id and property_id) ... the Add or Edit function only allows you to find a record based on one parameter. 

Michael Udinski

Joins are handled in the "By Joins" section of the Query All Records function... there's certainly a lot of information here, I'd suggest you sign up for an upcoming office hours if you are able to - it will probably be a lot easier to address your question over screen share

Kevin Wasie

 Yep, I'll be there. Thank you!

Khan aqib

 You have got the answer to this question??
I also had the same question.

Kevin Wasie


@ was able to help me minimize the logic and clean up the function stack significantly.

Utilizing the Find All Elements function inside the Array menu. 
[Screen Shot 2022-04-19 at 3.30.27 PM.png][Screen Shot 2022-04-19 at 3.33.28 PM.png][Screen Shot 2022-04-19 at 3.33.35 PM.png]

Khan aqib

 thank you so much