Imagine a scenario where I have the following 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;
}
The goal is to retrieve one random post along with its associated user, nested inside a user property within the object.
While utilizing the TypeORM repository class, this task can be achieved effortlessly:
public async getOnePost(): Promise<PostEntity> {
return await this.findOneOrFail({
relations: ['user']
});
This will provide a PostEntity object with a user property that contains a well-structured UserEntity object.
However, when it comes to fetching a random record from the database, the use of TypeORM QueryBuilder is necessary. I was able to come up with the following solution:
const array = await this.createQueryBuilder()
.select('*')
.from(PostEntity , 'post')
.leftJoinAndSelect('post.user','user')
.orderBy('RANDOM()')
.limit(1)
.execute();
return array[0];
After experimenting with the relation/join functions and parameters, I could only achieve:
- Only 1 property "userId" added to the PostEntity object
- "userId" property and all other user properties added in a "flat" manner to the PostEntity object
- "userId" property added, while all other user properties are incorporated "flat" with an alias: user_id, user_firstName, etc.
Therefore, using QueryBuilder, how can I acquire a Post structured like this:
{
id: 'e43c918c-55e1-4511-bce4-910fdd503548',
text: 'this is some text',
user: {
id: '123456789',
firstName: 'John',
lastName: 'Doe',
},
}