@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.
I create 3 collections:
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:
- Create review with the rating value 1:
- 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.
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.