How to select a specific column in typeorm many to many relationship

Question

I have an n:m relationship with a custom join table in TYPEORM.

Entity1

@Entity({ name: 'users' })
export class User extends BaseModel {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column({ type: 'varchar', length: 50 })
  @IsNotEmpty()
  username!: string;

  @Column({ unique: true, type: 'varchar', length: 50 })
  @IsEmail()
  @IsNotEmpty()
  email!: string;

  @CreateDateColumn({ type: 'timestamp' })
  createdAt!: Date;

  @UpdateDateColumn({ type: 'timestamp' })
  updatedAt!: Date;

  @DeleteDateColumn({ type: 'timestamp' })
  deletedAt!: Date;

  @OneToMany(
    (type) => UsersGameGroup,
    (userGameGroup) => userGameGroup.user,
    { cascade: true }
  )
  usersGameGroups!: UsersGameGroup[];
}

Entity2

@Entity({ name: 'game_groups' })
export class GameGroup extends BaseModel {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column()
  title!: string;

  @OneToMany(
    (type) => UsersGameGroup,
    (userGameGroup) => userGameGroup.gameGroup,
    { cascade: true }
  )
  usersGameGroups!: UsersGameGroup[];
}

Entity3 a Join table

@Entity({ name: 'users_game_groups' })
export class UsersGameGroup extends BaseModel {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column({ type: 'int' })
  userId!: number;

  @Column({ type: 'int' })
  gameGroupId!: number;

  @ManyToOne(
    (type) => User,
    (user) => user.usersGameGroups,
    { onDelete: 'CASCADE' }
  )
  user!: User;

  @ManyToOne(
    (type) => GameGroup,
    (gameGroup) => gameGroup.usersGameGroups,
    { onDelete: 'CASCADE' }
  )
  gameGroup!: GameGroup;
}

and I'm querying gameGroup to get the users.

const gg = await GameGroup.findOneOrFail(gameGroupID, {
        select: ['id', 'title'],
        relations: ['usersGameGroups', 'usersGameGroups.user']
      });
const { id, title, createdAt, updatedAt, usersGameGroups } = gg;

but the problem here is it will return all the columns of the user. All I want is the username.

return sample:

{
  "meta": {},
  "payload": {
    "gameGroup": {
      "id": 2,
      "title": "game2",
      "users": {
        "id": 2,
        "title": "game2",
        "usersGameGroups": [
          {
            "id": 2,  <-this too I don't need this but whatever
            "userId": 1, <-this too I don't need this but whatever
            "gameGroupId": 2, <-this too I don't need this but whatever
            "createdAt": "2020-04-09T00:11:39.000Z", <-this too I don't need this but whatever
            "updatedAt": null, <-this too I don't need this but whatever
            "deletedAt": null, <-this too I don't need this but whatever
            "user": {
              "id": 1,
              "username": "new1",
              "email": "new1@gmail.com", <- I just need this
              "createdAt": "2020-04-09T00:09:45.000Z",
              "updatedAt": "2020-04-09T00:10:55.000Z",
              "deletedAt": null
            }
          },
          {
            "id": 3, <-this too I don't need this but whatever
            "userId": 2, <-this too I don't need this but whatever
            "gameGroupId": 2, <-this too I don't need this but whatever
            "createdAt": "2020-04-09T00:12:10.000Z", <-this too I don't need this but whatever
            "updatedAt": null, <-this too I don't need this but whatever
            "deletedAt": null, <-this too I don't need this but whatever
            "user": {
              "id": 2,
              "username": "new2", <- I just need this
              "email": "new2@gmail.com",
              "createdAt": "2020-04-09T00:09:51.000Z",
              "updatedAt": null,
              "deletedAt": null
            }
          }
        ]
      }
    }
  }
}

And if I query it like this. I include the user and its username like user.username

relations: ['usersGameGroups', 'usersGameGroups.user', 'user', 'user.username']

I get an error.

"Relation \"user\" was not found, please check if it is correct and really exist in your entity."

---

In raw SQL the query looks something like this.

SELECT
    u.username
FROM 
    users u
JOIN 
    users_game_groups ugg
ON ugg.userId = u.id
JOIN 
    game_groups gg
ON gg.id = ugg.gameGroupId
WHERE gg.id = 2;

---

I'm expecting a JSON response like this.

"gameGroup": {
      "id": 2,
      "title": "game2",
      "users": {
        "id": 2,
        "title": "game2",
        "usersGameGroups": [
          {
            "user": {
              "username": "new1",
            }
          },
            "user": {
              "username": "new2",
            }
          }
        ]
      }
    }

Thank you!! <3

Answer 1

In my example, a user has submited their answers to some questionnaire, entity (1) is User related data that at many-to-many relationship to an entity (2) questionnaire, where i want the created_date of the submitted answer and the title of the questionnaire.

TypeORM entity query:

async getUserQuestionnaires(authorization: string) {
    const userValidated = await this.authService.validateAuthToken(authorization);

    return this.questionnaireUser.find({
      where: { userId: userValidated.data.id },
      relations: ['questionnaires'],
      select: {createdAt: true, questionnaires: { title: true}}
    });
  }

So essentially, 1. get the array of user submitted data, 2. get the relations, 3. on that select the columns i need.

Entity (1), one i'm querying to get data for:

@Entity('questionnaire_user')
export class QuestionnaireUser{
  @ApiProperty()
  @PrimaryGeneratedColumn()
  id: number;

  @ApiProperty()
  @Column({ type: "int4", unsigned: true, nullable: false})
  userId: number;

  @ApiProperty()
  @Column({ type: "int4", unsigned: true, nullable: false})
  result: number;

  @CreateDateColumn({name: 'created_at'})
  createdAt: Date;

  @ApiProperty()
  @Column({ type: "jsonb" })
  answers: userResponses;

  @ApiProperty()
  @ManyToMany(()=>Questionnaire, (questionnaires)=>questionnaires.userResponses)
  questionnaires: Questionnaire[];
}

Entity 2, the questionnaire of which i need the title

@Entity("questionnaire")
export class Questionnaire {
  @ApiProperty()
  @PrimaryGeneratedColumn()
  id: number;

  @ApiProperty()
  @Column({ type: "varchar", width: 255 })
  slug: string;

  @ApiProperty()
  @Column({ type: "varchar", width: 255 })
  title: string;

  @ApiProperty()
  @OneToMany(() => Question, (question) => question.questionnaire)
  question: Question[];

  @ApiProperty()
  @OneToMany(()=> QuestionnaireAnalysis, (analysis)=>analysis.questionnaire)
  analysis: QuestionnaireAnalysis[];

  @ManyToMany(()=> QuestionnaireUser, (userResponses)=>userResponses.questionnaires, {
    cascade: ["insert"],
  })
  @JoinTable()
  userResponses: QuestionnaireUser[]
}

Answer 2

After trial and error, reading the actual codebase and a full amount of not giving up, I finally got an answer.

const foo = await GameGroup.createQueryBuilder()
        .select(['gg.title', 'gg.id', 'ugg.id', 'u.username', 'u.email'])
        .from(GameGroup, 'gg')
        .innerJoin('gg.usersGameGroups', 'ugg')
        .innerJoin('ugg.user', 'u')
        .where({ id: gameGroupID })
        .getOne();

but I want it using active record.

Something like:

const gg = await GameGroup.findOneOrFail(gameGroupID, {
        select: ['id', 'title', 'createdAt', 'updatedAt', 'usersGameGroups'],
        join: {
          alias: 'gg',
          innerJoinAndSelect: { ugg: 'gg.usersGameGroups', u: 'ugg.user' }
        }
});

but I can't select from here though: I can't do gg.usersGameGroups.