hook to insert into a (different) collection, then query that collection within the same hook - query does not return new items...

[info68]

Hi,

I have created complex beforeInsert hook that is not fully behaving as I intend. The issue is slightly complicated, so I will explain the situation first.

The hook involves several queries and toInsert functions, which involve other collections, not just the collection directly associated with the hook.

There are 3 collections involved ( Client , Invoice , Tuition ).

The beforeInsert hook is associated with the Invoice collection, and follows this sequence (using async, await, try/catch blocks):

1. front end toInsert Invoice item > calls the beforeInsert hook.

2. hook queries Invoice collection > runs some functions (creates new invoice number, etc.) > assigns to new Invoice item.

3. hook then queries the Client collection > assigns relevant values to the Invoice item (client name, address, etc).

4. hook then uses various toInsert functions to add items to the Tuition collection (using values from both the Client and Invoice queries)

5. hook finally queries the Tuition collection (expecting to find return the newly inserted items which are used to calculate invoice balance). <<< this is the problem!

6. hook returns the Invoice item.

This issue is that (4.) inserts new items into the Tuition collection, then (5.) queries the Tuition collection - but the query does not return any of the newly inserted items, and the returned Invoice item does not have the correct value (balance).

If I run the Tuition query (4.) in a beforeUpdate(Invoices) hook, the query returns all the new items no problem and the returned Invoice item now has the correct value.

It is clear that items are not inserted into the Tuition collection until the entire hook has run through and returned the Invoice item, hence why the Tuition query doesn’t return the new items unless the hook has been completed…

I am running the sequence in try{await function }catch(e){} blocks, but it doesn’t make any difference.

Is there a way to force the insert to take place before the subsequent query (but the final query still take place within the same hook). Or am I trying to do something impossible?

Any advice would be greatly appreciated.

The work around I have at the moment is to run the query in a beforeUpdate query, and just arbitrarily update each item. But that is not practical!

I can post the code for the function if necessary, but I was hoping for a general “not possible” or “you need to do this instead” answer…

Many thanks in advance!

J

[givemeawhisky]

It would probably be a better option to try to break it down into a more controllable way of adding/inserting anything into the various collection, otherwise you could end up like you have got, with one thing wanting to happen before it has completed all the processes in your code.

Firstly, if you are wanting to try to work with async and await and use asynchronous code, then have a read of it here. - https://support.wix.com/en/article/corvid-working-with-promises

Otherwise, have a read of the datahooks reference, so that you can see if you can break it down into more sensible chunks or a easier way to get all that info added.
https://www.wix.com/corvid/reference/wix-data.Hooks.html
https://support.wix.com/en/article/corvid-about-data-hooks
https://support.wix.com/en/article/corvid-using-data-hooks
https://support.wix.com/en/article/corvid-tutorial-processing-user-input-before-it-is-stored-in-a-collection-with-data-hooks

Also, remember that with the datasets, you can always use onBeforeSave and onAfterSave instead of trying to do it all in datahook.
https://www.wix.com/corvid/reference/wix-dataset.Dataset.html#onBeforeSave
https://www.wix.com/corvid/reference/wix-dataset.Dataset.html#onAfterSave

Plus, you could also try adding something in your code that happens when values are changed in your dataset, as shown here.
https://www.wix.com/corvid/reference/wix-dataset.Dataset.html#onItemValuesChanged

You can also simply add a refresh in your code somewhere too so that it refreshes before you do your query which will then mean it should include any new added items etc.
https://www.wix.com/corvid/reference/wix-dataset.Dataset.html#refresh

Finally, just a thought, do you want to use Insert all the time or would you be better off using Update for some parts, remembering that Insert can’t overwrite any existing parts in your dataset?
https://www.wix.com/corvid/reference/wix-data.html#insert
https://www.wix.com/corvid/reference/wix-data.html#update

[info68]

Thanks for your extremely comprehensive response!

I think a big issue I have here is that I am working this mostly in the backend. There is no user input for my function (except clicking a button). I don’t care about elements on the page that hosts the button - I just need to ensure that all the data is retrieved/inserted correctly in between the various collections. There is no onready code, no datasets, just a button and an export function. The data from the collections will be displayed later on a separate dynamic page with relevant datasets.

The app I am working on just has a button which, when clicked, runs a basic loop that adds a series of new items to my Invoices collection (it creates a new invoice for each client in my database).

This in turn triggers the beforeInsert hook for each item (to insert the specific info for each client respectively).

This hook also triggers another backend function to retrieve events from a google calendar and insert these into a separate collection (Tuition) (which also triggers a separate beforeInsert hook which calculates the items’ prices).

Finally the hook runs a query to find the new Tuition items, and thus calculate the invoice’s balance). << but this doesnt work because it is searching the Tuition collection as it was before the hook was triggered…

I then also have an afterInsert hook, which is designed to remove any invoices from the collection, if they happen to have a balance of £0 (because there were no matching items in the Tuition collection.

At the moment this means that all my invoices automatically remove themselves as soon as being inserted. The current workaround has been to run the wixdata.remove in the afterUpdate hook instead, and update each invoice’s balance individually…
…

Something this issue has highlighted for me is perhaps the limitations of wixData API in the backend??

wixDataset seems much more versatile with many more functions available (refresh/onbeforesave/onaftersave/onitemvalueschanged/etc), but then the limitation of wixdataset is that it can only be used in the frontend code…

In my case I cannot run wixdataset.refresh to solve the issue, because a) my code is in the backend, and b) because I am not using a dataset! But then wixdata.refresh is not an available function, so I can’t use that either!

…

Does anyone have any idea why a query in the backend doesn’t return any new values when run (asynchronously) after inserting items into that collection?

It would be great to understand why this isn’t working for me, if it is possible to force some sort of refresh on the collection, or what the limitations are working with wixdata in this way in the backend??

For now I was planning to persevere with one of the following options:

1. Rewrite the problematic query as a separate function, then run this in the frontend after the Invoice items have been inserted (and thus the Tuition items have been inserted), and finally update each invoices’ balance accordingly.

2. Rewrite the problematic query as a separate function, then run this in an afterInsert hook and then wixdata.update the item to include each invoice’s newly calculated balance.

Any further advice would be greatly appreciated!

Thank you

[jhonychu93]

Any solution on this?