Polymorphic Associations

[CanRau]

Hey, very interesting project 🙌

Curious if polymorphism is already possible or planned?

E.g. I have a table commenting which has commentable_type & commentable_id fields where type could be article and id the articles id

I'm currently finally evaluating an ORM for our project as things get a little unwieldy using mysql2 directly writing manual types & queries 🥴

Edit: maybe this discussion on polymorphic associations could be interesting https://github.com/drizzle-team/drizzle-orm/issues/1051

[lroal]

Hello, thank's for your interest in RDB. It would not make sense to add polymorphism unless there is a way to add custom logic to the row. Today, this is not possible. I think this would be solved by the implementing hooks #60 instead , so you can add custom logic/class based on the type of the row. So am closing this issue as it is redundant when #60 is implemented.

[lroal]

Feel free to give some feedback, if i misinterpreted anything or didn't get the essence of your question. Maybe you can elaborate with a more detailed example ?

[lroal]

Reopening it, so @CanRau can elaborate with example and use case first.

[CanRau]

So I have multiple polymorphic tables, like commenting which has commentable_type & commentable_id to reference basically any row in any other table because we have a couple of tables which are "commentable"

Then we have likes, tagging tables which work the same and an assetable which basically works the same though joins an asset with whatever other table while also adding more meta data

If hooks can solve this that would be great, otherwise I'd have to pass. Which would also be totally fine, I'm not yet even sure what to use and it's also your project ❤️

With Drizzle it's already possible though they seem to be adding the option to specify a where clause on relation creation like so

export const usersRelations = relations(users, ({ many }) => ({
  comments: many(comments, {
    where: eq(comments.commentType, 'user')
  }),
}));

which from an api perspective might look like this in rdb

order: x.user.map(({ hasOne, hasMany, references }) => ({
    likes: hasMany(x.likes).by('likeableId').where(x.likes.likeableType.eg(x.user.id))
  }))

or something like that 🤔

[lroal]

This can be solved by using the columns disciminators. It hasn't been documented yet, but your case would make an excellent example. When setting the column discriminator to commentable_type='user', any comment that is inserted will set the column commentable_type to value 'user' automatically. And vice versa for selecting.

One thing I notice, is that you would like to use the column, commentable_id, for both referencing city and user. This will work, but you will not be able to have a physical FK constraint in the database. A better solution would be to have explicit fields for userId and cityId in the comments table. This will also make it easy to fetch directly in comments table and traverse to correct parent. I propose both solutions below.

With same column for parent table:

const map = rdb.map((x) => ({
    user: x.table('user').map(({ column }) => ({
      id: column('id').numeric().primary(),
      name: column('name').string(),
    })),

    city: x.table('city').map(({ column }) => ({
      id: column('id').numeric().primary(),
      name: column('name').string(),
    })),

    userLikes: x.table('comment').map(({ column }) => ({
        id: column('id').numeric().primary(),
        userId: column('commentable_id').numeric().notNull(),
      }))
      .columnDiscriminators(`commentable_type='user'`),

    cityTags: x.table('comment').map(({ column }) => ({
        id: column('id').numeric().primary(),
        cityId: column('commentable_id').numeric().notNull(),
      }))
      .columnDiscriminators(`commentable_type='city'`),
  }))
  .map((x) => ({
    user: x.user.map(({ hasMany }) => ({
      likes: hasMany(x.userLikes).by('userId'),
    })),
    city: x.city.map(({ hasMany }) => ({
      tags: hasMany(x.cityTags).by('cityId'),
    })),
  }));

Optimal solution with explicit columns for city and user:

const map = rdb
  .map((x) => ({
    user: x.table('user').map(({ column }) => ({
      id: column('id').numeric().primary(),
      name: column('name').string(),
    })),

    city: x.table('city').map(({ column }) => ({
      id: column('id').numeric().primary(),
      name: column('name').string(),
    })),

    userLikes: x.table('comment').map(({ column }) => ({
        id: column('id').numeric().primary(),
        userId: column('user_id').numeric().notNull(),
      }))
      .columnDiscriminators(`commentable_type='user'`),

    cityTags: x.table('comment').map(({ column }) => ({
        id: column('id').numeric().primary(),
        cityId: column('city_id').numeric().notNull(),
      }))
      .columnDiscriminators(`commentable_type='city'`),

    comments: x.table('comment').map(({ column }) => ({
      id: column('id').numeric().primary(),
      cityId: column('city_id').numeric(),
      userId: column('user_id').numeric(),
      type: column('commentable_type').string(),
    }))    
  }))
  .map((x) => ({
    user: x.user.map(({ hasMany }) => ({
      likes: hasMany(x.userLikes).by('userId'),
    })),

    city: x.city.map(({ hasMany }) => ({
      tags: hasMany(x.cityTags).by('cityId'),
    })),

    comments: x.comments.map(({ references }) => ({
      user: references(x.user).by('userId'),
      cityId: references(x.user).by('cityId'),
    }))
  }));

[CanRau]

Interesting I'll give that a try when I get the time 😃

Foreign key constraints aren't supported by Vitess and therefore Planetscale so not an issue 😄

Edit: now I'm curious what you were thinking initially with custom row logic etc?

[lroal]

With custom row logic I was thinking that you could just wrap a Class around the row - like a proxy. In that way, the row would have methods as well. Assume customer row has email. Then add a method notifyUnpaidInvoices() the Customer class that sends email about unpaid Invoices. The Class that is returned must play together with intellisense so that getOne wil return that Class unioned with the Row type. I need to do some research how this would look like.

[CanRau]

Ah that sounds very interesting 😃 Still haven't really had the time to play with rdb 🥲

[lroal]

Enjoy 🙂 I am closing this issue for now