Filtering on One to Many Relationships
Consider a schema with 3 tables:
- Animal (base table) - one row per animal (e.g. lion, bear, horse, trout, eagle...)
- AnimalColor - one row for each animal/color combo, where each animal could have more than one color (one to many). Ex: bear/black, bear/brown, bear/white, horse/beige, horse/white...
- AnimalFood - one row for each animal/food combo, where each animal could have more than one food (one to many). Ex: bear/fish, bear/honey, bear/berries, horse/grass, horse/hay, eagle/fish...
I want to build a dashboard that allows someone to filter on color and/or food, and displays a list of matching animals. For example, if I select "brown" and "fish" I might get bear and eagle in the results. The point is that I've got multiple one to many relationships to a base table, but Incorta joins only allow one to one (Incorta will just pick one row if there are multiple matching rows in the child table).
Is there a way to implement something like this? I'd like to keep "Animal" as my base table, but have filters that show me the values for "color" and "food" and I can select from those and it will return all animals that match to the selected colors/foods.
I run into this use case quite frequently, and it sounds simple but the one to many relationships make it more difficult than it should be.
The only solution I've come up with thus far is to create an MV that has a delimited list of colors/foods for each animal (e.g. bear | black,brown;white | fish,honey,berries) and then use a "contains" filter on the delimited list. But this is not ideal as it requires creating an MV and the "contains" filter isn't as elegant as being able to see a list of values and select from it.
Dan - This is a usecase of a common parent for two base tables is covered in my community article https://community.incorta.com/t/q5s5kp/modeling-schemas-tables-and-joins ,
We need to create a bridge mv with three fields - animal, Color and food and join all the three tables as parents .
Thanks for describing the problem so well.
I used your data that you described to create following video. This video follows the same approach that Amit requested you to try.
Link to Video:
In the original description of this problem, there's one really important aspect that I didn't emphasize, and using the suggested bridge table approach I'm still running into some problems.
Take the original problem, but assume the base "animal" table has two measures in it: EstimatedPopulation and AverageWeight.
I want to create a dashboard that allows a user to select multiple values for "color" and "food" and then produce an aggregate table that shows: Total Est Population, Total Estimated Weight.
If a user filters color to brown and black and filters food to fish, the result might match on bear (for both brown & black), eagle (for brown only). But I only want to add up the estimated population for bear once, I don't want to double count bears because they matched on two different colors. But the bridge table suggested solution will double count the animal measures.
In other words, when the same "animal" is returned more than once in the result set, I only want to include the measures once instead of repeating them multiple times for each match.