Many 2 many relationship via join table

[CanRau]

Can't wrap my head around how to define a many-to-many relationship via a join table and couldn't really find anything of help in the repo. Might've been looking wrong tho 😇

Could you share a simple example please?

Also out of curiosity it looks like you decided to switch from something like db.rollback to rolling back by throwing an Error? What's the reasoning/benefit?

[lroal]

You are right, it is not there. Traditionally, I've approached this by using an explicit binding table. This method establishes a clear one-to-many relationship with the binding table first, and then a standard reference or join relationship to the target table. This has been my preferred method because it maintains a level of transparency and explicitness in the data model.

I do recognize that many-to-many relationships can sometimes introduce an impedance mismatch, making the reasoning about the database-to-code mapping less intuitive. This is because it can conceal an underlying level of abstraction that I believe should be more transparent. In real-world scenarios, such as the relationship between students and the courses they attend, we often need to store additional information like registered_at timestamp and grade, which fits naturally in a binding table format.

The design decision around the primary key in the binding table—whether to use a composite key made up of the primary keys from the linked tables or to introduce a separate primary key—is indeed not a one-size-fits-all solution and requires careful consideration for each unique situation.

While I hold some reservations about many-to-many relationships due to these complexities, I am open to exploring its implementation in RDB. In the meantime, you need to handle this with the use of a binding table.

There is no specific reason for the missing db.rollback method (other than being generally very catious what I expose from the "core" to the public typescript layer) . A colleague asked me about the same the other day. I will create a separate issue for it.

[CanRau]

Sorry should have been more clear I'm actually referring to a many to many relationship with a binding table, which in my case also holds additional data

What are you referring to when talking about many to many? 😅

I'm curious though it looks like I'm going with kysely which gives me more dynamic query building with pretty good typescript support instead of a more opinionated orm.
We'll see how that goes 🤓

[lroal]

When I refer to many to many relationship, I mean modelling the binding table and the joined table as one entity in the orm mapping. Example is prisma version of many-to-many . This is not possible in RDB, and you would need to go via hasMany and a reference relation instead.
E.g. a student that is enrolled in courses could be represented be 3 physical tables in the database:
Student
id
name

Enrollment
id
studentId
courseId
grade

Course
id
description

Use case : get all students with courses that has grade B in maths.

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

  enrollment: x.table('enrollment').map(({ column }) => ({
    id: column('id').numeric().primary(),
    studentId: column('studentid').numeric(),
    courseId: column('courseid').numeric(),
    grade: column('grade').string(),
  })),

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

})).map(x => ({
  enrollment: x.enrollment.map(({ references }) => ({
    course: references(x.course).by('courseId')
  }))
})).map(x => ({
  student: x.student.map(({ hasMany }) => ({
    enrollments: hasMany(x.enrollment).by('studentId')
  }))

}));

const db = map.sqlite('demo.db');

//get all students with courses that has grade B in maths.
const filter = db.student.enrollments.any( x => {
  return x.grade.eq('B').and(x.course.description.eq('maths'));
});
const students = await db.student.getMany(filter, { enrollments: { course: true } });
console.dir(students, {depth: Infinity});

output:

[
  {
    "id": 1,
    "name": "Lars-Erik",
    "enrollments": [
      {
        "id": 2,
        "studentId": 1,
        "courseId": 100,
        "grade": "B",
        "course": {
          "id": 100,
          "description": "maths"
        }
      },
      {
        "id": 3,
        "studentId": 1,
        "courseId": 200,
        "grade": "C",
        "course": {
          "id": 200,
          "description": "history"
        }
      }
    ]
  }
]

What use case do you have which is better to solve in Kysely ? I am curious and interested in helping.
As you can see in the example above, you need to combine hasMany and references in order to get what you want.
As there is no hasManyToMany() mapping method today, this is the approach.

[lroal]

It is now possible to elevate child columns to parent tables.
This pretty much does the same as many-to-many relations.
So I am closing the issue.
Example from https://github.com/alfateam/orange-orm#user-content-aggregate-results

import map from './map';
const db = map.sqlite('demo.db');

getRows();

async function getRows() {
  const orders = await db.order.getAll({
    balance: x => x.customer.balance
  });
}