Dynamically update parent field based on calculation of value across child elements

Need help Database
1

I am doing a test project to learn about Bubble’s capabilities, and I can’t figure out something.

The test project is a Yelp clone. So basically, I have a parent “Restaurant” object with child “Review” objects. For relevance to this question, each Review object has 2 fields: a numeric Rating and a written Comment.

I want this Yelp clone to be map-focused, and I want the map markers for Restaurants to be color-coded based on the average rating across the child Reviews.

It seems that Bubble’s native Maps will not be able to populate different marker colors (please correct me if wrong), so I’m going to use the Mapbox plugin, which also has a workflow where a list of Restaurants can be populated with custom icons. After consulting with Mapbox, here’s how it will work:

  1. I will define an Option Set, named “AvgRating” that allows me to define a numeric set of ratings with distinct images (in my case, a colored marker)
  2. The Restaurant object will have a field that takes this AvgRating Option Set

Putting this together, the Mapbox workflow will list all Restaurants, and then pull the colored marker from the Restaurant’s AvgRating’s image.

That’s great, I can make that work.

HOWEVER >> now I need to know, how can I update the AvgRating field on the Restaurant object, as new child Reviews are added?

Is there a way to do this real time, so in a sense the AvgRating field becomes dynamic, or do I have to do some kind of daily background job/workflow?

ALSO, it’s important to mention that I’m totally open to completely new ways of resolving this, as this is just a learning test for me. As a developer, how I would have done this is a dynamic calculation where an icon is pulled based on a calculation of each parent Restaurant’s average rating across all child Reviews. But I’m not sure this is possible… hence the workaround to define a static AvgRating.

2

@james46 this seems appropriate for a backend workflow. When a User adds a review for a Restaurant, you could schedule a backend workflow that runs an update to the AvgRating field for the Restaurant based on the List of Child Reviews under that Restaurant. No need to make it a recurring daily/periodic workflow - just run the workflow when a review is added. Happy to provide more guidance if needed.

3

Thanks! That seems like a great plan. Any chance you can also take a look at this question? Change view depending on if user is logged in or not

It’s slightly related!

4

@londonhere , Ok… I cannot seem to implement this workflow. The workflows all seem to hinge on an action happening on the front end (e.g., the button to “Create Review” is clicked).

HOWEVER, if I just want to create a workflow based on the creation of a new Review (whether user hits “Create Review” OR I manually create it in the App Data section), is that not possible?

5

My suggestion is to create a Backend workflow called something like “updateavgrating”. That workflow should take in a parameter for the restaurant item that needs to be updated (i.e. “pRestaurant”). There should be an action in that backend workflow to make a change to pRestaurant, updating the AvgRating based on the new set of reviews.

In the front end, when a User clicks Create a review and finishes submitting, that workflow should include a Schedule an API Workflow action for the “updateavgrating” backend workflow.

6

Interesting, so it still neds to be triggered by Front End user action - there’s no way to just trigger workflows on database changes in general (whether manually triggered or by front end user action) I take it?

7

There’s several options for you here and you can try testing them and checking your server logs to see what’s requiring more workload.

Setting a database trigger on the backend is a valid option. The trigger could watch for any changes to restaurant review ratings, then trigger a backend event to update the respective restaurants avg rating if there’s a change.

In the cases I’ve tested database triggers, they have typically been more expensive on workload than triggering an event based on a user action.

8

Hi @james46 ,

Inside a worklfow after adding a new review, can’t you add another step “Make changes to thing…” with “Do a search for” looking at the restaurent, and then through another search gather all review related to that restaurant and making the average ?

9

Yep you can do that too - just might slow things down a little on the user side with a big search.

The other thing to consider is that this update average rating function is probably a function you might want to execute from different pages in the app. In that case, it may be helpful to put that function in the backend and just call it from wherever.

10

@londonhere yes that’s precisely why I want to do a backend workflow, even if it’s more expensive in terms of resources.

Can you help me figure out how to do it exactly? I’m just doing this to learn Bubble and I can’t seem to figure it out. I assume it’s a Custom event, but I can’t figure out how to capture the trigger, which is basically when any Create/Update/or Delete action is taken on the Review database.

11

@james46 I recommend watching this tutorial on backend workflows from BuildCamp: https://www.youtube.com/watch?v=Kd9pLy4ob8I

It’s a pretty good walkthrough on all the steps, but happy to help if you’ve got questions not addressed there.

12

That was a good tutorial thank you! However, it appears to not have worked, wondering if you have a sec to check out this Loom? Loom Message - 21 November 2024 | Loom

I made the backend workflow, scheduler is not paused.

One thing that I wonder… you’ll see that when a Review is updated (old Rating is not same as new Rating), the workflow is updating the Market. It’s looking for the Market based on unique id = Review now’s Parent Market’s unique id. I DID set this, but notice on the Loom at 0:17, how when I click to toggle this out, it shows as RED, as if the search query was incorrectly formed, but when I click on the RED text, the correctly formed query appears… this isn’t the first time I’ve seen this, I’m struggling to figure out what this UI means?

In any case, in the rest of the Loom you see me going into App Data to make a modification on a Review, which succeeds, and then you see me going into Server Logs, it looks like the backend workflow DID run, but however, the final update doesn’t work, as the Market doesn’t have MapIcon = 4 (for now I’m just doing any changes to Review Ratings causes the MapIcon to equal 4)