How to conditionally add a new record, when a null is present in the ID?

aemondis

Hi All, fairly new here migrating to Xano, moving away from bubble.io thanks to their completely misguided pricing model changes. Having a few challenges trying to migrate some core parts of the DB into Xano. I come from a DBA background, so I'm quite comfortable with the underlying structure.

My specific use case is that I have several related tables (which are queried first for performance reasons), and only if a 3rd table has data - it should then be updated, but if the relevant record in the 3rd table is missing, it should be created. What this means in practice, is I have a query with several joins (inner & left joins), with an addon in the output from that query for the data in the 3rd table. In cases where the 3rd table has no record, I get a null for the addon in the output from the main query. This results in an error being thrown as: Exception: Param: field_value - Missing param: field_value.

What I was expecting was that if a null value is provided as the field_value, that this would be interpreted as "a new entry should be created, since the existing couldn't be found or doesn't exist", but instead I get an error that the value is missing. I could do a workaround with a conditional if/then to check if that field is null, but this adds complexity and slows down the processing, and seems like a simple thing Xano should be able to help with: if the lookup field is null, assume that record doesn't exist and add it.

Unless I'm missing something?

P.S. I am using the "id" field in the 3rd table (which does the add/edit record) for the lookup, to ensure I am matching the existing records atomically.

endrefyl

Hi! I solved it with an IF-statement. IF 0 or empty/null, then, else.. :)

Pawel Magdanski

You have a Add or edit function that should do the trick

aemondis

So I think I have come to the realisation that joins in Xano are not the same as regular MSSQL etc., where you can add multiple joins and filter out relevant fields either within the JOIN itself or in the WHERE clause. I was thinking in this logic, and as it turns out - that simply doesn't work. The behaviour does not act as I would expect (I've been a DBA in a previous life for 10+ years).

My issue turned out to be that the addon was not being populated at all, which was upsetting the add or edit record function, due to me thinking in the MSSQL way. In essence, I was filtering a value from a table ~3 steps removed from the primary table, which was actually removing way more than it should have. As a DBA and also developer, I usually try to avoid loading data into an app to iterate, if there's a better way to do the query directly in the DB - but seems in Xano that isn't the case.

As such, my solution was to convert my logic into a loop and query only tables joined over 1 step, then everything works normally. If I add a table 2 steps removed, then things start acting weird.

On a side note… I wish Xano had a DB query builder that I could develop the queries in real-time, rather than trial-and-error using an API function and breaks!

Pawel Magdanski

If you are joining table in query via join then you should be able to use the joined table to do some filtering in custom query. Is it not working for you?

aemondis

Hi Pawel,

Yeah I tried that, and it wasn't working. I was seeing the expected addons refusing to output data, as I was getting null values for fields that should have been returning data. In the end, I gave up trying to use joins and iterate through it with a loop instead, which works fine in this instance since it's not a heavily-used API.

Pawel Magdanski

Hi,

You mentioned addons, and I was thinking about join feature. I want to be sure that we are thinking about the same thing.

Joins are here

And addons are here

Which one did you tried? Both? Can you show how it looks like in function stack?