0

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.

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

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

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

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

      Reply Like
  • 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

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

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

      https://www.youtube.com/watch?v=FJgFiDvToUY

      Reply Like
Like Follow
  • 1 mth agoLast active
  • 7Replies
  • 149Views
  • 6 Following