ER_BAD_FIELD_ERROR When trying to query Many to Many relationship

[aaronhawkey]

When I try to include the 'platforms' entity, which is a many to many relationship, I receive an ER_BAD_FIELD_ERROR.

Here are my Entities: https://pastebin.com/c6GC1ui6

Controller Logic:

import { Controller } from '@nestjs/common';
import { Crud } from '@nestjsx/crud';
import { TournamentService } from './tournament.service';
import { Tournament } from './entities/tournament.entity';
@Crud({
    model: {
      type: Tournament,
    },
    query: {
        join: {
            platforms: {
                eager: true,
            },
            game: {
                eager: true,
                exclude: ['createdAt', 'modifiedAt'],
            },
        },
    },
})
@Controller('tournament')
export class TournamentController implements CrudController<Tournament> {
    constructor(public service: TournamentService) {}
}

When removing the platforms join, the game join works perfectly. Maybe I am new and Many to Many relations are no longer supported?

I followed this medium article: https://medium.com/@rodrigo.tornaciole/nest-js-many-to-many-relationship-using-typeorm-and-crud-ec6ed79274f0 and one of the sample projects using crud.

[aaronhawkey]

Been doing some more playing around and it seems that I cannot have my @PrimaryGeneratedColumn anything else besides type int and a name of 'id'? Is this true, because there is nothing in the documentation about this.

If I change my @PrimaryGeneratedColumn to include an type of int and column name of id, the many to many relation works?? This has to be a bug or something is missing in the docs. ManyToOne relationships aggregate perfectly fine with a uuid and any type of name. Any insights here?

[binarytracer]

@hselaaron

Been doing some more playing around and it seems that I cannot have my @PrimaryGeneratedColumn anything else besides type int and a name of 'id'? Is this true, because there is nothing in the documentation about this.

about this statement, i think this is not true, im currently using now with @PrimaryGeneratedColumn as string and with _id, as my id field. And it works fine.

@Entity()
export class Client {
  @PrimaryGeneratedColumn('uuid')
  client_id: string;

[aaronhawkey]

@binarytracer Thank you so much for your reply.

Working with a UUID outright is just fine, but did you have any issues establishing many to many relationships and joining them like in my example?

Edit: Here is the commit with the CRUD dependency and how I am using it. I have removed CRUD at the HEAD for now. https://github.com/hselaaron/tournament-test/tree/716ed7b5bfa69cd3458ce5b09364e8f24d72c28d/src/tournament

[binarytracer]

many to many is fine, also.

You just need to add @JoinTable to specify local field connecting to remote fields and etc.

you can have info here

here is the example

  @ManyToMany(() => <Entity_2_here>, { cascade: true })
  @JoinTable({
    name: <pivot_table_here>,
    joinColumn: { name: <local_field>, referencedColumnName: <local_field> },
    inverseJoinColumn: { name: <remote_reference_field_here>, referencedColumnName: <remote_reference_field_here> },
  })
  project_tags: <Entity_2_here>[]; // NOTE: ephemeral field, use for relationship.

hope this helps.

[aaronhawkey]

Other engineers within my org were able to reproduce this issue. 🤷‍♂ Is there something specifically wrong in the configuration I have given above?

Again, there is specifically something wrong with my implementation of using a UUID with a manytomany. Specifically when calling a Get to /tournament. I am able to get them to work individually. I am also able to get it to work with the same exact configuration, but instead of specifying UUID, specifying an auto incrementing int.

Thanks again for your help.

[michaelyali]

@hselaaron I wonder whether it might be related to this issue https://github.com/typeorm/typeorm/issues/3151?

[aaronhawkey]

@zMotivat0r It totally could be on typeorm's side. Its not the same error that is described, but its affecting the same exact things. I don't have the best knowledge to determine that outright. I have asked in the TypeORM slack community and it seems that they don't know themselves, but are able to replicate this behavior with the CRUD library.

I am currently manually writing out CRUD functionality on this controller, and the many to many is working fine with the UUID PK currently. Which makes me wonder...

Thanks!

[binarytracer]

@hselaaron one thing I notice from what youve given, is that you missed the field referencedColumnName, in @JoinTable.

   joinColumn: { name: 'project_id', referencedColumnName: 'project_id' },
    inverseJoinColumn: { name: 'project_tag_id', referencedColumnName: 'project_tag_id' },

few things I would like to add also are the following:

• using postgreSQL.
• typeorm synchronized = true, I saved a lot of time here.
• table specified on @ManyToMany will be generated with the fields defined joinColumn and inverseJoinColumn.

Can you add errors you've seen here?

[aaronhawkey]

@binarytracer Good evening and thanks for the follow up. I added those fields onto the join & inverse columns, and I still get the same error I have been getting. Tested that before I opened the ticket, but didn't commit that work to the repo. My bad. I have created a new branch on that github repo called 'crud' (https://github.com/hselaaron/tournament-test/tree/crud) that includes the @jointable changes.

I am currently using MySQL v5.7. I have TypeORM configured in './config/database.config.ts', where synchronized is set to true.

Here is my screenshot of replicating the error this evening:

Thanks again for your attention on this issue.

[michaelyali]

@hselaaron could you please create a repo with the generated nest project and those two modules (platforms, tournaments). I'll try to debug and help you as much as I can. Also, please take a look at the example here: https://github.com/nestjsx/crud/tree/master/integration/crud-typeorm users and projects have ManyToMany too

[michaelyali]

@hselaaron another thing that might be worth mentioning - please try to add alias as following https://github.com/nestjsx/crud/blob/f4e634dfd0d03473cec41b96a4b63caa118d7f13/integration/crud-typeorm/users/users.controller.ts#L35

[michaelyali]

Oh, I totally missed the fact that you've already sent a link for you project :( sorry for bothering

[aaronhawkey]

@zMotivat0r Haha! No worries! Thanks again for your help!! Again, I have created a branch specifically for CRUD on that repo since I deleted the dependency on master.

Thanks!!!

[aaronhawkey]

@zMotivat0r Any luck? 😄

[binarytracer]

Im just curious @hselaaron , does the mysql vesion 5.x support UUID?

[aaronhawkey]

@binarytracer I believe it does. I haven't had any issues before. https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html

[Darkein]

I have many to many relations in my project and it works well

I can't see your entities (the link is dead), but last time I had this kind error it was because of a small mistake in the ManyToMany dataattribute:

I typed

  @ManyToMany(type => Recruitment, form => form.id)
  recruitments: Recruitment[];

insteadOf

  @ManyToMany(type => Recruitment, form => form.forms)
  recruitments: Recruitment[];

maybe you could just check if everything is correct, or if your project works when you use typeorm directly ?

[aaronhawkey]

@Darkein Thanks for your response. It does work directly with typeorm.

[Darkein]

can you try to exclude the joined table of your relation in your controller ?

@Crud({
  model: { type: User },
  query: {
    join: {
      companies: { exclude: ['users'] },
    }
  }
})

[aaronhawkey]

@Darkein Same thing happens.

Here is the link again to the code: https://github.com/hselaaron/tournament-test/tree/crud If you remove the platforms join, the 500 server error goes away. I even upgraded to MySQL 8.0, and the problem persists.

Here is a dump of some test data: https://pastebin.com/rYsgHnXg