orderBy oneToMany relation using typeorm

Question

I have PublicationsEntity with a OneToMany relation to LikesEntity, i'm trying to sort my publications by likes, and return all of the likes with the user associated in likes, and all the comments to display them front-side

@Entity('publications')
export class PublicationsEntity {
        @PrimaryGeneratedColumn()
        id: number;
    
        @Column({ type: 'longtext' })
        publication: string;

        @ManyToOne(type => UsersEntity, users => users.publications, { 
        onDelete: "CASCADE" })
        user: UsersEntity;

        @OneToMany(type => CommentsEntity, comments => 
        comments.publication, { onDelete: "CASCADE" })
        comments: CommentsEntity[];
    
        @OneToMany(type => LikesEntity, likes => likes.publication, { onDelete: "CASCADE" })
        likes: LikesEntity[];
    }

there is my likes entity who contains user and i'm trying to return each user on each like after with the sorted publications :

@Entity('likes')
export class LikesEntity {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    like: number;

    @ManyToOne(type => UsersEntity, user => user.likes, { eager: true })
    user: UsersEntity;

    @ManyToOne(type => PublicationsEntity, publication => publication.likes)
    publication: PublicationsEntity;
}

my users entity :

@Entity('users')
export class UsersEntity {
    @PrimaryGeneratedColumn()
    id: number;

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

    @OneToMany(type => PublicationsEntity, publications => 
    publications.user, { onDelete: "CASCADE" })
    publications: PublicationsEntity[];

    @Column()
    email: string;

    @Column({ select: false })
    password: string;

    @OneToMany(type => CommentsEntity, comment => comment.user, { onDelete: "CASCADE" })
    comments: CommentsEntity[];

    @OneToMany(type => LikesEntity, like => like.user, { onDelete: "CASCADE" })
    likes: LikesEntity[];

    @OneToMany(type => PublicationsEntity, publications => publications.user, { onDelete: "CASCADE" })
    publications: PublicationsEntity[];

    @CreateDateColumn({ type: "timestamp", default: () => "CURRENT_TIMESTAMP(6)" })
    createdAt: Date;

    @UpdateDateColumn({ type: "timestamp", default: () => "CURRENT_TIMESTAMP(6)", onUpdate: "CURRENT_TIMESTAMP(6)" })
    updatedAt: Date;
}

and there is my publicationsService

async getPublications() {
    return await getConnection()
    .createQueryBuilder(PublicationsEntity, 'publications')
    .leftJoinAndSelect('publications.likes', 'likes')
    .leftJoinAndSelect('publications.user', 'user')
    .leftJoinAndSelect('publications.comments', 'comments')
    .leftJoinAndSelect('comments.children', 'children')
    //.orderBy()
    .getMany()
}

some data on my likes table : https://i.sstatic.net/wJ0l5.png

Answer 1

First of all, I think you should fix something on ur entities In your PublicationsEntity

 @OneToMany(type => LikesEntity, likes => likes.publication, { onDelete: "CASCADE" })
    likes: LikesEntity[]; // this one should be an array

and for LikesEntity

@ManyToOne(type => PublicationsEntity, publication => publication.likes)
publication: PublicationsEntity;

For your question, I assume that you want to return the Publication sorted by number of likes, to do that you should add in your query:

async getPublications() {
return await getConnection()
.createQueryBuilder(PublicationsEntity, 'publications')
.leftJoin('publications.likes', 'likes')
.addSelect("COUNT(likes.publication) AS total_likes" )
.orderBy('total_likes', 'ASC') // or DESC
.groupBy("likes.publication")
.getRawMany()
  }

UPDATE

As I understand from your comments below, I think you need to use the map and sort function to get the result you needs :

async getPublications() {
return await getConnection()
.createQueryBuilder(PublicationsEntity, 'publications')
.leftJoinAndSelect('publications.likes', 'likes')
.leftJoinAndSelect('publications.user', 'user')
.leftJoinAndSelect('publications.comments', 'comments')
.leftJoinAndSelect('comments.children', 'children')
//.orderBy()
.getMany().then(data => data.map(a => ({ ...a, total_likes:a.likes.length })).sort(function (a, b) {
  return b.total_likes - a.total_likes; // this's going to sort by DESC if you want to make the opposite just do a.total_likes - b.total_likes
})
)

}