TypeORM: Select RANDOM() with a nested relation using QueryBuilder

Question

Lets say I have these entities:

User

@Entity('user', { synchronize: true })
export class UserEntity {

  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column()
  firstName: string;

  @Column()
  lastName: string;

  @OneToMany(type => PostEntity, post => post.user)
  posts: PostEntity[];

}

Post

@Entity('post', { synchronize: true })
export class PostEntity {

  @PrimaryGeneratedColumn('uuid')
  id: string;

  @ManyToOne(type => UserEntity , user => user.posts)
  @Index()
  user: UserEntity ;

  @Column({ nullable: true })
  text: string;
}

What I'm trying to get is one random post, together with it's related user, nested inside a user property on the object.

Using the actual TypeORM repository class I can do that easily:

  public async getOnePost(): Promise<PostEntity> {
    return await this.findOneOrFail({
      relations: ['user']
    });

This results in a PostEntity object with a user property which is in itself a well structured UserEntity object.

But for getting a random row from the DB, it seems I have to use TypeORM QueryBuilder. I managed something like this:

const array = await this.createQueryBuilder()
  .select('*')
  .from(PostEntity , 'post')
  .leftJoinAndSelect('post.user','user')
  .orderBy('RANDOM()')
  .limit(1)
  .execute();
return array[0];

Playing around with the realtion/join functions and parameters I was only able to get:

1. only 1 property "userId" added to PostEntity object
2. "userId" property and all other user properties added "flat" to the PostEntity object
3. "userId" property added, while all other user properties are added "flat" with an alias: user_id, user_firstName, etc.

So, using QueryBuilder, how can I get a Post like this:

{
  id: 'e43c918c-55e1-4511-bce4-910fdd503548',
  text: 'this is some text',
  user: {
    id: '123456789',
    firstName: 'John',
    lastName: 'Doe',
  },
}

Answer 1

I ran into the same problem trying to do raw queries, the solution I found was to use JSONB_BUILD_OBJECT()

https://www.postgresql.org/docs/9.5/functions-json.html

Basically your query would look something like this:

select
  post.*,
  jsonb_build_object(
    'id', user."id",
    'firstName', user."firstName",
    'lastName', user."lastName"
  ) "user"
from...

I'm curious to know if there's an elegant way to do it with typeorm clause, but when I run into these API issues I usually just go with raw query.

Answer 2

This worked for me:

import { Entity, PrimaryGeneratedColumn, Column, Unique} from 'typeorm';

@Entity({ name: 'users' })
@Unique(['email'])
export class User {
  @PrimaryGeneratedColumn()
  id: number;
  ...
}

const userRepository = myDataSource.getRepository(User);
const randomUser = await userRepository
      .createQueryBuilder('user')
      .select()
      .orderBy('RANDOM()')
      .getOne();

Got it from here https://www.kindacode.com/snippet/typeorm-how-to-select-random-rows/