Polymorphic Associations?

Hey all - I'm coming to drizzle-orm + SvelteKit from the Rails world.

I want to have polymorphic associations, as documented here: https://guides.rubyonrails.org/association_basics.html#polymorphic-associations

This is what I have in my schema.ts:

Ruby on Rails Guides

Active Record Associations — Ruby on Rails Guides

Active Record AssociationsThis guide covers the association features of Active Record.After reading this guide, you will know: How to declare associations between Active Record models. How to understand the various types of Active Record associations. How to use the methods added to your models by creating associations.

rphlmr ⚡•8/22/23, 9:28 AM

Hello,
You should look at Relational queries: https://orm.drizzle.team/docs/rqb.

I think this is what fits the must with what you want.

hacktographerOP•8/22/23, 1:52 PM

Hello @Raphaël M (@rphlmr)

thanks for the tip.

hacktographerOP•8/22/23, 1:54 PM

So with the polymorphic relationship, I have 2 columns which combine to create a relationship between different tables. In my case, I have and

the would define a relationship between and some other table, while would tell me which row from the table referenced by I should retrieve.

hacktographerOP•8/22/23, 1:58 PM

In this example, a might have a membership in both the and tables, and we use to say one relationship is with and the other is with . Two different rows in the same table, both with the same but with the value referencing the field of either the or . The documentation for Relational Queries appears to have the table hard-coded. Maybe I could make that dynamic instead somehow?

hacktographerOP•8/22/23, 1:59 PM

Ah I just now saw this: https://github.com/drizzle-team/drizzle-orm/issues/207

GitHub

Add UNION support · Issue #207 · drizzle-team/drizzle-orm

rphlmr ⚡•8/22/23, 1:59 PM

ok I see now.
The relational api is to create a prisma like api when 2 tables are joined

hacktographerOP•8/22/23, 2:01 PM

Right - but it might be multiple tables. A "user" could have a "membership" to multiple types of "groups". Staff, Insured Members, Affiliate Marketers, Service Providers, etc.

rphlmr ⚡•8/22/23, 2:01 PM

you mean, having x foreign keys to tables?

hacktographerOP•8/22/23, 2:02 PM

It's ok to not have foreign keys, I'm just looking for some syntactical sugar so I can get a list of a user's memberships to different groups

hacktographerOP•8/22/23, 2:04 PM

for future reference, here's the github discussion about this topic for Prisma: https://github.com/prisma/prisma/issues/1644

GitHub

Support for Polymorphic Associations · Issue #1644 · prisma/prisma

I have the following schema: model Post { id String @default(cuid()) @id } model Comment { id String @default(cuid()) @id } model Vote { id String @default(cuid()) @id item Post | Comment } I'd...

rphlmr ⚡•8/22/23, 2:04 PM

Ah yes it reminds me something

rphlmr ⚡•8/22/23, 2:05 PM

i use a similar pattern like you but not that deep, for a cms I'm building.
I have a table with a type (enum like, but a string literal), an id col, user_id col, and common columns.
Then I have as many tables as I have types.

rphlmr ⚡•8/22/23, 2:05 PM

A sort of inheritence pattern

hacktographerOP•8/22/23, 2:05 PM

right -- like this in rails: https://www.freecodecamp.org/news/polymorphic-association-ruby-on-rails/

freeCodeCamp.org

How to Use Polymorphic Associations in Ruby on Rails

Polymorphic association in Ruby on Rails refers to the type of active record association. From the Rails Guide [https://guides.rubyonrails.org/association_basics.html#polymorphic-associations] , it allows a single model to belong to more than one other model on a single association. This tutorial assumes that you have some knowledge of a few ass...

hacktographerOP•8/22/23, 2:06 PM

I think it's a common pattern, and I'd like to have some nice syntax for it -- and before I start trying to implement something on my own I'd like to be sure that the feature doesn't already exist in Drizzle ORM.

rphlmr ⚡•8/22/23, 2:08 PM

my implementation results in many joins.

that I consolidate next in my code with a rule that said that if the 'reference type is x' then the join's result for this table can't be null.

rphlmr ⚡•8/22/23, 2:09 PM

I don't think it will exist in core Drizzle api

hacktographerOP•8/22/23, 2:09 PM

ahh ok I see. This is clear enough, and solves the problem

hacktographerOP•8/22/23, 2:09 PM

Since this is such a common pattern, it might be worth adding to the official documentation.

hacktographerOP•8/22/23, 2:10 PM

IMO it's not necessary that something like this is built-in to the tool, as long as the effect can be achieved.

rphlmr ⚡•8/22/23, 2:10 PM

Sadly I can't share you the whole picture, but you got it

hacktographerOP•8/22/23, 2:10 PM

yeah that's nice -- I'll try with this style. Thank you so much!

rphlmr ⚡•8/22/23, 2:11 PM

We need a shadcn for Drizzle haha

rphlmr ⚡•8/22/23, 2:11 PM

Sharing code that others can copy paste as a base

hacktographerOP•8/22/23, 3:13 PM

Dear Future Reader: Here is a stackoverflow answer that gives an example of how to achieve this pattern using only SQL: https://stackoverflow.com/a/2333543

Stack Overflow

SQL Conditional / Case Joining / Polymorphic Associations?

I'm trying to implement something similar to Ruby on Rails' polymorphic relationships.
I have the following three tables :

Events
Users
Organisations

An event can be owner by either a user or an

dion•9/25/23, 5:20 PM

Hey! Still kind of confused as to how you create a polymorphic association in drizzle.

Could you be kind enough to provide some code on your implementation? (i.e. changes you made to your schema.ts) That owuld help me alot. @hacktographer

CanRau•10/27/23, 6:43 PM

also trying to figure out how to get my schema with multiple polymorphic tables going with drizzle

also found https://github.com/drizzle-team/drizzle-orm/issues/1051

GitHub

[FEATURE]: Support Polymorphic Association · Issue #1051 · drizzle-...

Describe what you want I'm looking for a Typesafe ORM that support for polymorphic associations. To give a concrete example: I have a Comment model. I need this model to be associated with both...

Angelelz•10/28/23, 3:50 AM

I just added a comment on that issue on how this can be achieved. I tested locally and the results are very promising