Tips on handling multiple join conditions (composite key) between 2 data models?

jingyu · February 14, 2023, 2:21am

I noticed that only 1 join condition can be defined when creating a relationship between 2 data models in Holistics:

We have 2 models that should be joined on 2 columns:

Month
User ID

Currently, we create surrogate keys within the data models (i.e. concat of Month and User ID), and defined the relationship based on the surrogate key. However, I’m not sure if I’m misunderstanding how the UI works, and whether there are more intuitive ways to achieve the above.

I would be happy to hear suggestions or how others are approaching this scenario!

daph.td · February 17, 2023, 7:50am

Hi Jing Yu @jingyu,

Thank you for sharing your questions on our community! It’s been a while since your last post.

Your approach to “create surrogate keys within the data models (i.e. concat of Month and User ID), and defined the relationship based on the surrogate key” is spot on. Currently, we only support that approach.

But I could definitely see how we could improve our feature based on your question. I will be sure to add your input to our backlog for further consideration.

Cheers!
Daphne

Guido_Stark · February 23, 2023, 8:24pm

We’d like to back this request too. We have a number of models that could join by Date+ID, or Type+ID and are currently creating a number of surrogate keys. This feature would provide much better functionality for working just in the Holistics platform.

cjm-shiftsmart · December 11, 2023, 9:32pm

I would like the same feature

thanh · December 20, 2023, 3:51am

There is a workaround for this use case in our documentation here Relationship on Composite Keys. Does it solve your problem?

In the future we may integrate this workaround directly into the product so you can directly create composite key relationship.