Many-to-many relations with custom properties select

[Alexx1992]

Issue type:

[x] question [ ] bug report [ ] feature request [ ] documentation issue

Database system/driver:

[ ] cordova [ ] mongodb [ ] mssql [ ] mysql / mariadb [ ] oracle [ ] postgres [ ] cockroachdb [x] sqlite [ ] sqljs [ ] react-native [ ] expo

TypeORM version:

[ ] latest [ ] @next [x] 0.x.x (0.2.18)

Steps to reproduce or a small repository showing the problem:

Hi! I have many-to-many relations with custom properties. For example:

@Entity()
export default class Student {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    firstName: string;

    @Column()
    lastName: string;

    @Column()
    age: number;

    @OneToMany(() => StudentClassRoom, sc => sc.student)
    studentClassRoom: StudentClassRoom[];
}

@Entity()
export default class ClassRoom {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  hasDesk: boolean;

  @OneToMany(() => StudentClassRoom, sc => sc.classRoom)
  studentClassRoom: StudentClassRoom[];
}

@Entity()
export default class StudentClassRoom {
  @PrimaryGeneratedColumn()
  id: number;

  @ManyToOne(() => Student, student => student.studentClassRoom)
  student: Student;

  @ManyToOne(() => ClassRoom, classRoom => classRoom.studentClassRoom)
  classRoom: ClassRoom;
}

How should my query look like to get something like this:

[
  {
     id: 1,
     firstName: 'Jack',
     age: 22,
     classRoom: [
       {
          id:2,
          name: 'History',
          hasDesk: true
       }
     ]
  },
  {
     id: 2,
     firstName: 'Kate',
     age: 21,
     classRoom: [
       {
          id:1,
          name: 'Art',
          hasDesk: false
       },
       {
          id:3,
          name: 'Math',
          hasDesk: true
       }
     ]
  }
]

I have tried with left/inner join, map but to no avail( Thanks!

[csotiriou]

That was exactly my question, as well. It seems that when following the "custom properties" approach, you lose the facility of the @ManyToMany decorator, which is to perform a join by just using leftJoinAndSelect.

I would love to see a response to this.

[ghost]

Interesting point, looking for an answer as well.

[frankjrangel]

Yes, I don't get why the documentation shows you how to do it but doesn't tell you how to make the query to get that relation when selecting. Would love to know how to do this properly.

[dennisameling]

I built the following query:

return getConnection()
    .getRepository(Student)
    .createQueryBuilder('student')
    .leftJoin('student_class_room', 'scr', 'scr.studentId = student.id')
    .leftJoinAndSelect('class_room', 'classroom', 'classroom.id = scr.classRoomId')
    .getSql();

This generates the proper SQL statement:

SELECT `student`.`id` AS `student_id`, `student`.`firstName` AS `student_firstName`, `student`.`lastName` AS
`student_lastName`, `student`.`age` AS `student_age`, `classroom`.`id` AS `classroom_id`, `classroom`.`name` AS
`classroom_name`, `classroom`.`hasDesk` AS `classroom_hasDesk` FROM `student` `student` LEFT JOIN `student_class_room`
`scr` ON scr.studentId = `student`.`id` LEFT JOIN `class_room` `classroom` ON `classroom`.`id` = scr.classRoomId

The SQL output is also correct:

... the JSON output only misses the classrooms (while they are in the SQL output):

[
    {
        "id": 1,
        "firstName": "Jack",
        "lastName": "Sparrow",
        "age": 20
    },
    {
        "id": 2,
        "firstName": "Kate",
        "lastName": "Test",
        "age": 15
    }
]

Seems like TypeORM doesn't know which property on the Student model it should use to bind the classrooms to.

Laravel has a HasManyThrough option that allows you to define a pivot table (in this case StudentClassRoom) to get the final data (in this case classrooms). If TypeORM has a similar function that'd be perfect for this case, so that would be a new feature I guess?

[thellimist]

any update on this?

[geilsonrm]

any update on this?

[cizza]

I have the same problem and I guess everybody else, who is actually using custom many-to-many. Please, can we have a response from the team or anyone who can answer? How to query for the relation on the m-t-m with custom fields to receive the relation, not the pivotTable? I find this is quite a big issue. Thank you.

[BouweCeunen]

Indeed, this is very blocking. Response should really be appreciated.

[snolangps]

is this still an issue? I'm also trying to get around this simply.

[sarlt001]

Also facing the same issue!!! Please help support

[incompletude]

Same

[fav1c0n]

I am in the same situation. Help please!

[incompletude]

I think this is working, at least is what my tests tell.

I have a user:

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number

  @OneToMany(() => Subscription, (subscription) => subscription.user)
  subscriptions: Subscription[]
}

Then i have a subscription:

@Entity()
export class Subscription {
  @PrimaryGeneratedColumn()
  id: number

  @ManyToOne(() => User, (user) => user.subscriptions)
  user: User

  @ManyToOne(() => Plan, (plan) => plan.subscriptions)
  plan: Plan
}

And then I have a plan:

@Entity()
export class Plan {
  @PrimaryGeneratedColumn({ name: "id" })
  id: number

  @Column()
  name: string

  @Column()
  price: number

  @OneToMany(() => Subscription, (subscription) => subscription.plan)
  subscriptions: Subscription[]
}

Then in my UserRepository I can:

    const user = await this.userRepository
      .createQueryBuilder("user")
      .leftJoinAndSelect("user.subscriptions", "subscriptions")
      .leftJoinAndSelect("subscriptions.plan", "plan")
      .where(`user.id = ${id}`)
      .getOne()

[ganlanshu0211]

in nestjs, I use repository solve this

[adrianwix]

@ganlanshu0211 How did you solve it?

[snolangps]

You're not setting the PrimaryColumn's... In your StudentClassRoom you'd need: ` @PrimaryColumn() studentId!: number

@PrimaryColumn() classRoomId!: number `

[adrianwix]

Ok. Do you mind if I made a bit of off topic question? How do you save the entities? Let's say you want to save a new Classroom with two new students. Before I would do like

const classroom = new Classroom()
const student1 = new Student()
const student2 = new Student()
classroom.students = [student1, student2]
classroom.save()

If you have an entity that act as a join table how would you do it?

[Noctis-Solaith]

Could you try something like that?

(async () => {
    try {
        const connection = await createConnection();
        const queryResult = await Student.find({
            relations: ['studentClassRoom', 'studentClassRoom.classRoom'],
        });
        console.log(queryResult);
    } catch (error) {
        console.log(error);
    }
})();

[xiaosha007]

any new update?

[bduff9]

2 years later and no answer on this? This seems like a very big deal for something that is documented but doesn't seem to work. Is it possible to get some idea of if this will ever be fixed or if the documentation will be fixed to remove this as a possibility?

[imnotjames]

2 years later and no answer on this? This seems like a very big deal for something that is documented but doesn't seem to work. Is it possible to get some idea of if this will ever be fixed or if the documentation will be fixed to remove this as a possibility?

It works in some cases but not others. If you believe that it should be removed from the documentation, PRs for documentation improvements are accepted and encouraged. :)

[bduff9]

I appreciate the response, but I'm sorry, that's not really helpful. This ticket has been open for 2 years and by your own words, it's known that this doesn't always work. As a casual user of this library, I have no idea in what way to modify the docs since it doesn't seem there is a way to get this to always work based on reading through the comments in this issue. Should it be removed as an option from the docs completely? Should it be dev work to fix these bugs? Is there some secret way to get this to work that only the devs know? It's not at all clear from reading the docs.

Which was my point, that the docs are not only not totally complete (as mentioned in this issue) but also misleading. Docs are usually great for people learning how to use a tool (and Typeorm's are better than a lot of other libraries) but in this case they don't tell the whole story without someone also managing to find and read through this issue. Ideally, known bugs would be fixed, which is what I think I was most surprised about, that this was not after 2 years. If there is no intention to fix these issues, then I would expect the issue to be closed with a comment to that effect and at minimum, the docs to be updated to say this is buggy and doesn't always work. /r

Sorry for the rant and I certainly don't mean any offense here but I was very surprised to see something that was documented does not work after we lost a day of dev trying to figure out why. When I found this ticket and read through it, I was just trying to understand if this is something that will ever be fixed or if not, if the docs will be fixed to better indicate that this does not fully work. Which is what I am still hoping to learn, will this be fixed or will the docs? :)

[imnotjames]

I appreciate the response, but I'm sorry, that's not really helpful.

This ticket has been open for 2 years and by your own words, it's known that this doesn't always work.

To clarify, it's an issue report, not a ticket. A ticket to me implies there's an SLA. We don't have one. Most of the maintainers and contributors of this project (eg, myself and a few others) have no actual agreement nor compensation for our work. Except a warm and fuzzy feeling.

As a casual user of this library, I have no idea in what way to modify the docs since it doesn't seem there is a way to get this to always work based on reading through the comments in this issue. Should it be removed as an option from the docs completely?

Possibly. That's what I was suggesting.

Should it be dev work to fix these bugs?

That would be even better! We're accepting PRs and I do my best to review PRs as I can.

Is there some secret way to get this to work that only the devs know? It's not at all clear from reading the docs.

No, there's not.

Which was my point, that the docs are not only not totally complete (as mentioned in this issue) but also misleading. Docs are usually great for people learning how to use a tool (and Typeorm's are better than a lot of other libraries) but in this case they don't tell the whole story without someone also managing to find and read through this issue.

Docs are community provided for the most part. Please feel free to add more or remove outdated bits. My best guess is that these docs were provided by someone but they did not add context.

Ideally, known bugs would be fixed, which is what I think I was most surprised about, that this was not after 2 years.

Someone has to prioritize and fix it. My priorities lay elsewhere but I do find it frustrating as well that the documentation is lacking. It is something I've taken a couple attempts at but I've yet to find a path forward that I like to improving the docs long term.

If there is no intention to fix these issues, then I would expect the issue to be closed with a comment to that effect and at minimum, the docs to be updated to say this is buggy and doesn't always work.

I leave the issue open because it's still an issue and somebody needs to update the docs. That somebody at this time is not me.

/r

Sorry for the rant and I certainly don't mean any offense here but I was very surprised to see something that was documented does not work after we lost a day of dev trying to figure out why.

No offense taken, friend! That does sound incredibly frustrating.

When I found this ticket and read through it, I was just trying to understand if this is something that will ever be fixed or if not, if the docs will be fixed to better indicate that this does not fully work. Which is what I am still hoping to learn, will this be fixed or will the docs? :)

The docs seem to be the faster fix to this. I don't believe anyone in this issue has claimed they're investigating it to fix it.

Like I said. Someone's got to take the first step to make that happen. I am not planning on it at this time because I have other areas where I'm focusing at this exact moment in time. In particular, splitting the drivers and improving queries across relationships.

I hope this helps! Also, should you want to take the initiative here I'd be happy to pair with you and help you with that. :) I just got a day job too, y'know?

[akbarhabiby]

in nestjs, I use repository solve this

I also use Nest.JS and TypeORM, how do you solve this problem using a repository? Are u using a custom repository or maybe mapping the results or what?

[basitalisandhu]

Am new to typeorm and facing the issue. For custom relations we have to first get the id for single row and then get the data against id from another table. is there any better way to achieve the same result.

@imnotjames am happy to fix this but for that i need some support from you. Let's have a session.

[alessandroprudencio]

You're not setting the PrimaryColumn's... In your StudentClassRoom you'd need: ` @PrimaryColumn() studentId!: number

@PrimaryColumn() classRoomId!: number `

I tested this and it worked great.

The only issue is that now there are 2 primary keys in the same table and with the same name, i don't know if this is a good practice or not!

[temurih]

Almost 4 years, but still no good solution to this? Unbelievable!

[adrianwix]

@temurih migrate to Prisma. Save yourself the trouble. Prisma is highly mantained and backed by investors. It is not that hard. You will have some technical debt for a while but the DX is in another level

[temurih]

@adrianwix you might be right. I liked their documentation, which is why I donated to the project, and I was planning to donate more. But I guess I can look into Prisma.