How would you retrieve products where the two latest reviews are positive?

Hi,

Say I have a table of product review scores that looks something like this, in descending order of creation - so the latest is at the top:

Prod | Score
XYZ | 5
ABC | 4
ABC | 2
XYZ | 3
XYZ | 2
ABC | 4

Now what I want is to retrieve a list of products where the last N reviews for the product were positive, which we should take to mean 3 or higher (to keep this simple let’s assume that this number is always 3). On the other hand, for our example, let us assume that N is 2 but that this needs to be dynamic, i.e. is to be specified by the end user or retrieved from the database. So, based on our value of 2 in this example, we should retrieve only XYZ since its last two scores are 5 and 3 while for ABC they are 4 and 2 so ABC is excluded.

How would you do this? I’m finding that the inability to do custom sorting on a group-by in Bubble is making this rather challenging and I haven’t found a way around it.

A few things to note:

  1. Please feel free to recommend additional columns/tables to achieve a solution.
  2. Ideally, the solution will not be dependent on a RG as I may need to do this from the server as well. That being said, I would be grateful even for a RG-based solution at this point.
  3. Efficiency and performance will be important but again, I would appreciate anything that works at all in the first instance. In other words don’t let performance concerns get in the way of a suggestion.

I hope that I’m missing something simple or obvious here as I have been off Bubble for several months and may be a little rusty.

Thanks.

@louisadekoya

A suggestion would be to iterate through the list to place the first two items in another list that meet the conditions either on the browser or in the server.

If on the browser explore the plugin “list shifter” by @keith. @boston85719 is very knowledgeable about this plugin and many times contributes freely with awesome ideas should you want to go beyond this current scenario. There is also a great forum thread about the use of that plugin.

If on the server, explore doing the iteration recursively scheduling a backend workflow (those that go through a list and perform the needed actions one entry at a time and stop via an extinguishable condition in order to avoid an infinite loop).

1 Like

@cmarchan , thanks I’m looking at List Shifter now. I had only looked at it briefly before. There appears to be a lot that it can do so I’m hopeful.

1 Like

@louisadekoya I don’t think the list shifter plugin is necessary, but it should help if you just want to leave things as is otherwise.

Reading your need I think it might be a bit easier to just update a field on the data type you are using to reference the products themselves. Have a field that would be a list of numbers and maybe label it ‘latest_reviews’.

When a new review is add run a backend workflow to make a change to this list of numbers by changing the value. Then you could do a simpler constraint on the search to fetch the products whose list of latest reviews number is greater than or equal to 3…not sure exactly which constraint would work appropriately, but I’d personally assume it will be simpler as I don’t mind the extra step of backend workflow to use an ‘extra’ data field.

Thanks @boston85719 . I’ll experiment a little to see which option performs better etc. I expect a lot of product records and reviews so I’m mindful of the 10 list field limit, but I may be able to get around this with housekeeping.

My recommendation wasn’t to save all the reviews as a list…I was suggesting to save only the most recent two review scores.

I see. Hmm, the thing is though, as I mentioned in my original post, the number 2 needs to be variable and dynamic per user. So one user may be interested in the last 2 reviews, another may want the last 5 etc

Yeah, I didn’t read the idea of N reviews as being a variable number. In that case I wouldn’t bother with adding anything to the product…instead add a field to the review that is related to the product. Do a search for reviews and constrain by the product and then set up your dynamic number of reviews using a custom state to determine how many of the most recent reviews to return and setup your comparisons.

1 Like