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 .Reply
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:Reply