I have a page that supports reviews of products (say, product X, Y, or Z).
One database has four columns: a rating (a number), a comment (string), the name of the product (a string), and a Boolean (True/False). The database is populated through a form submission, with the exception of the Boolean. The administrator can put the Boolean to True if it approves the rating/comment. On the website, a front-end function checks if the Boolean is true (aka the review is approved); if so, it shows the rating and comment on the page.
I now need a second database with as many rows as the number of items (one row for X, Y, and Z) that looks into the first database, and if the review is approved (Boolean is True), it averages the ratings in the first database and puts it in the corresponding product row.
In other words, this second database would self-update based on the values of the first database that has been administrator-approved. Ideally, it would average the rating of all approved comments. Is this possible?
Currently, I do have a second database with the average review values that is updated upon the form submission - but this surpasses the administrator approval. One option is to use a front-end function that retrieves all reviews from product X that were approved and averages them. However, with all the products and reviews, the database may have upwards of 50k rows, which likely makes this process very slow.
Would you have any suggestions on how to do this? Thank you so much!
Hi! Yes, it is possible to achieve this functionality in Wix Code. You can use the “wix-data” module to query the first database and retrieve only the approved reviews for each product. Then, you can calculate the average rating and update the second database using the “wix-data” module again.
Thank you so much for your comment! I appreciate your time and help.
I understand that one option is to use a front-end function that retrieves all reviews from product X that were approved and averages them.
However, I am preparing for the possibility of having 50k or more rows. I am afraid that a front-end function might be too slow.
Is there a way to have this calculations always made in the back already? In other words, this second database would self-update based on the values of the first database that has been administrator-approved. Ideally, it would average the rating of all approved comments. Is this possible?
Yes, it’s definitely possible to have the calculations made in the back-end already. One approach you can take is to use a Wix Backend module to perform the averaging calculation and update the second database. Note that performing these calculations in the back-end can help improve the performance of your application, especially if you have a large number of reviews. It also ensures that your data is always consistent and up-to-date, since the back-end function will always be triggered whenever a new review is approved. Good luck!