[FEATURE]: "One of many" relationship

[MatanYadaev]

Describe what you want

A model can have a one-to-many relation, but sometimes you might want just the "latest" or the "oldest" record of this relation. "Has one of many" relationship helps to accomplish that.

const authorsRelations = relations(authors, ({ many }) => ({
  books: many(books),

  // Feature request:
  latestBook: latestOneOfMany(books),
  oldsetBook: oldestOneOfMany(books),
  cheapestBook: oneOfMany(books, [
    { column: 'price', function: 'max' },
    { column: 'id', function: 'max' },
  ]),
});

Prior Art

• Laravel Eloquent:
  • Docs: https://laravel.com/docs/10.x/eloquent-relationships#has-one-of-many
  • PR: [8.x] Add one-of-many relationship (inner join) laravel/framework#37362

[erfanium]

@MatanYadaev
You can create a custom sql view for that. like author_latest_books.
then your author table has a one-to-one relationship with author_latest_books.

[MatanYadaev]

@erfanium Of course I can do stuff manually, but I prefer the ORM to do that for me. Thanks :)

[erfanium]

@MatanYadaev It's a bit off topic: ORMs can be incredible unmaintainable, if you say yes to every feature request. that's why most ORMs suck and they will be abandoned after a while.

you start with latestOneOfMany and oldestOneOfMany. then someone else will come and say i need maxOneOfMany and minOneOfMany. then another guy says i need countOfMany and averageOfMany. this simply never ends.

I'm not a drizzle-orm contributor currently, but personally i don't want it to go that way, like other similar projects.

[L-Mario564]

Closing this in favor of #821. The feature request in the mentioned issue would lead to a more dynamic implementation of what's described here.