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.

10replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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 .

    • Amit Kothari Thanks Amit.   Dan Nielsen , I think your MV approach is close to what we'd recommend given the current product behavior (aka. Release 4.3) (although you can just create the linking table with the Cartesian product key the Amit alluded to instead of a delimited list).  We are working on handing M:M relationships in an easier way in future versions of the product.

    • Thanks for the replies. I'll check out Amit's approach and I look forward to more robust M:M relationships in the future.

    • Dan Nielsen  Just create a Materialized View (using the SQL option) that serves as a "bridging" table with each row containing the three IDs enumerating each combination of attributes.  Then everything else will join to this new MV table as parent tables.

  • Hi Dan,

    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:

    - https://drive.google.com/open?id=1JauCH9-ZTnEXh1jHdm38ZqrR3aXd_Akv

    • Anurag Malik Thank you for the video. You have inadvertently highlighted one of my frustrations in creating more than one child join. While you can hit the plus to add another child join, you cannot actually do it. You must hit done for each join and come back in to create a new one. This seems like a bug.

    • Please use this video. Link above may not work.


    • Marc Paige Marc, that plus sign when creating a join is not for creating another child join.  It is for creating a composite join condition (aka. joins conditions that involve multiple fields).  Individual child joins across different tables have to be defined one join at a time.

    • Dan Brock Thanks for clarifying Dan.

  • 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. 

Like Follow
  • 2 mths agoLast active
  • 10Replies
  • 182Views
  • 6 Following