Getting lost down a rabbit hole of many to many relationships

[AlexB]

Hello,

I am a few days in and several attempts into Adalo to build an mvp but I am struggling with the basic DB relationship side of things.

I am trying to create a page where a user can review a business with a review of either a happy, neutral or sad face.

A user can review multiple businesses - one to many
A review can only apply to one business - also one to many - but what i don’t understand is, because a review is an entity that can be used again and again, should a review be set up as one to many or many to many relationship?

And should each review type, happy, sad neutral have it’s own collection?

I have watched several help vidoes, but I am no clearer on the relationship.

Many thanks
Alex

[jeremy]

Hey @AlexB I would probably do just the two one-to-many relationships you described. The many-to-many doesn’t sound necessary because each individual review is just for one business. The review type (happy, neutral, or sad) would probably best be saved just as text, and then you can use that result wherever you want to show / hide things or display the status.

[karimoo]

@AlexB Hi Alex, I have been struggling with many-to-many relationships for a while now. So took your question as a challenge to dig a little bit deeper.
My idea was to create something like this:

Beside the design challenge with the stars, the question for the data model is the same.

Collection Setup
I create 3 collections:

Users

Company

Reviews

Reviews -> Users: one-to-many
Reviews -> Company: one-to-many

In addition to the relations for Users and Companies I added two text fields to store the unique identifiers for the reviewing user and the reviewed company.
We need this to set the filters for the visibility.

Actions to create the review

When the user clicks on the first star to actions are performed:

1. Create review with the rating value 1:
   
   
2. Update company
   

For the second and third star only the rating value changes:

The stars to set the rating a grouped and only visible, when there is no review of the Logged In User for the Current Company:

The star rating display is done with 5 groups of stars with conditional visibility:

This works fine without using the additional fields in the review collection, because we can use the Average function of the numeric Rating value.

Why the additional fields for Reviewer Email and Company Name?
I want to display the rating the user entered the first time:

When I add a text label and try to add the value of the current users rating AND the current company, I only find this:

When starting from the Current Company, there is no way to filter on current user to display the rating value. Only Count is available.
Same problem when starting from Logged in User. No way to add the second filter for Current Company to display the Rating Value the user selected.

What is the trick?
The trick to get the display looks like this:
First I selected the text label and Make List

With this new list, I can now grab some additional data - the Current Company > Reviews:

The list is based on collection Reviews and filtered for the Current Company > Reviews.
The second filter to show only the logged in users rating is added using the additional filter Reviewer email is equal to Logged In User > Email.

Hope this was not to long and confusing and I did not mix it up.

In summary:
As @jeremy suggested, you only need one-to-many relationships here.
I recommend to add the key fields of the related collections in addition, if you need filtering on more than one related collection to set conditional visibility.

I would also go to use rating values, which are easy to calculate averages. You can also change the rating easily by just adding +1 or -1 to the current value to up or down vote.
If you need to use images instead of the stars, you can do this in a separate collection mapping the rating value to the corresponding image.

[AlexB]

Thank you @jeremy and @karimoo for your incredibly detailed response. I am blown away by the level of detail you have gone into. I have found it really helpful.

I am however stuck, trying to recreate your steps. If you have the time I would really appreciate the help.

What relationship did you give your user to your company?

When trying to create the review from the click In the Company Name field I can’t find Company name. I have the same issue as the text labels, the endless list of options.

I just get this

The closest I have managed to get to is this:

Many thanks
Alex

[karimoo]

@AlexB Hi Alex, looks like you started with single components for the three buttons.
Are these Action Buttons where you changed the icon?
Action buttons are pre-build components which some behaviours I never fully understood.

I recommend to start with your own custom list like this:
First add a Text label, you will need it later.
Then add an Ellipse with an Icon Button on top of it.

Select the Ellipse and the Icon Button and group it.

Hold the Alt/Option key and drag& drop the group to duplicate it for the second and third button.
Change colours and icons.
Select all three and AND the Text label. Choose Make List.

Select the Company collection as basis for the new list:

That is the starting point I used in my example.

When you now create an new action on the icon buttons it looks like this:

But you can only work with the Logged In User, but cannot select a Company:

The reason is that Adalo can only handle single records.
You need to specify the company that should get the review.

If you come from a previous screen, where you selected a company, you should already have Current Company available on your screen.

You can check this by clicking the name of your screen on the canvas and open Available Data:

The option in my example screen is to use a dropdown menu to select the company to review.

and filter the list below.

If you now do a preview and select a Company from the dropdown you don’t see your button.
You need at least one property of the Company to be in the list. That is why we started with the text label.

Change the text label in the list to Current Company > Name:

If you edit the action for the icon buttons you can select the name of the current company:

If you are completely new to Adalo, I recommend to watch the introduction videos here::Full Walkthrough - Adalo Resources