How to limit and orderby left join results and map them into one single field in TypeORM

user_9854107 0
Viewed 1450

I have two models named Activity and ActivityAttendants. An activity can have many activity attendants (1:M). Here is my both TypeORM models

Activity Model

export class Activity {
  @PrimaryGeneratedColumn("increment")
  activity_id!: number;

  @Column({ nullable: false })
  title!: string;

  @OneToMany(() => ActivitiyAttendant, (activity_attendants) => activity_attendants.activity, {
    cascade: true,
    onDelete: "CASCADE",
    onUpdate: "CASCADE",
  })
  @JoinColumn({ referencedColumnName: "activity_id" })
  activity_joined!: ActivitiyAttendant[];
}

ActivityAttendants Model

export class ActivitiyAttendant {
  @PrimaryGeneratedColumn("increment")
  activity_attendant_id!: number;

  @Column({ comment: "joined user profile picture" })
  profile_picture!: string;

  @ManyToOne(() => Activity, (activity) => activity.activity_joined)
  @JoinColumn({ name: "activity_id" })
  activity!: Activity;
}

For brevity, I removed all other unnecessary fields.

Now when I do left join ActivityAttendants with Activity, I expect the output like below:

[
  {
    activity_id: 2,
    title: "Sharable clear-thinking conglomeration",
    recent_attendants: [
      {
        activity_attendant_id: 2110,
        profile_picture: "1f525.svg",
      },
      {
        activity_attendant_id: 271,
        profile_picture: "1f4ce.svg",
      },
      {
        activity_attendant_id: 67,
        profile_picture: "1f525.svg",
      },
    ],
  },
  {
    activity_id: 3,
    title: "Upgradable fault-tolerant emulation",
    recent_attendants: [
      {
        activity_attendant_id: 2697,
        profile_picture: "1f469-1f3ff-200d-2764-fe0f-200d-1f48b-200d-1f468-1f3ff.svg",
      },
      {
        activity_attendant_id: 402,
        profile_picture: "1f1f9-1f1e8.svg",
      },
      {
        activity_attendant_id: 208,
        profile_picture: "1f9ef.svg",
      },
    ],
  },
];

Where recent_attendants is the array of 3(not more) most recent attendants inserted into ActivityAttendant (it will have a createdAt field for orderby) table.

Here's what I've done so far.

 activityRep
  .createQueryBuilder("a")
  .leftJoinAndMapMany("a.activity_joined", "a.activity_joined", "aj")
  .select([
    "a.activity_id AS activity_id",
    "a.title AS title",
    "aj as recent_attendants",
  ])
  .getRawMany();

It's just doing group concat, returning one JSON for every element in the recent_attendants field. Moreover, it's impossible to do orderby and limit this way.

Is it possible to get expected output using TypeORM querybuilder or I have to do the data shaping programmatically?

typeorm left-join postgresql orm database
1 Answers

If I'm understanding your question correctly:

You want:

{
  activity_id:3,
  title: 'Fubar Activity',
  activity_joined: [
    {
      activity_attendant_id: 25,
      profile_picture: 'http://www.example.com/user1.jpeg'
    },
    {
      activity_attendant_id: 8,
      profile_picture: 'http://www.example.com/user2.jpeg'
    }
  ]
}

But you're getting:

[

  {
    a_id: 3,
    a_title: 'Fubar Activity',
    aa_activity_attendant_id: 25,
    aa_profile_picture: 'http://www.example.com/user1.jpeg'
  },
  {
    a_id: 3,
    a_title: 'Fubar Activity',
    aa_activity_attendant_id: 8,
    aa_profile_picture: 'http://www.example.com/user2.jpeg'
  },
]

if that's the case, try getMany() instead of getRawMany()

user_13665900
Related