CODEWITHSUNDEEP
nestjstypeorm
Pankaj Kumar
Pankaj Kumar

Reputation: 181

Is there a way I can use Group By and Count with Type Orm Repository

I am new here recently joined and New in Type ORM My code that I am trying

FIRST QUERY: I would like to use this approach but not sure how I can group by with count on the column and then order by desc on that column

 const result = await this.jobViewsRepository.find({
        relations: ["jobs"],
        loadEagerRelations: true,  
        order: { id: "DESC" },
        skip: offset,
        take: limit,
    }
    );

I am trying if I can use this in my above query

SECOND QUERY: IT'S WORKING FOR ME PERFECTLY THE RESULT I AM LOOKING

    const res = await this.jobViewsRepository.createQueryBuilder('jobViews')           
    .addSelect("COUNT(jobViews.user_id) AS jobViews_total_count" )
    .leftJoinAndSelect(Jobs, "jobs", "jobs.id = jobViews.job_id")
    .where("jobs.user_id != :id", { id: user_id })        
    .groupBy("jobViews.job_id")**
    .orderBy('jobViews_total_count', 'DESC')**
    .limit(limit)
    .offset(offset)           
    .getRawMany();

Please if any can help me out in this will be really appreciated

Thanks

Upvotes: 18

Views: 31247

Answers (3)

Bohdlesk
Bohdlesk

Reputation: 85

Now is the only one way is to use queryBuilder And .groupBy("user.id") with .addGroupBy("user.id")

https://orkhan.gitbook.io/typeorm/docs/select-query-builder#adding-group-by-expression

Or write raw query:

 import { getManager } from 'typeorm';

  const entityManager = getManager();
  const someQuery = await entityManager.query(`
  SELECT 
    fw."X",
    fw."Y",
    ew.*
  FROM "table1" as fw
  JOIN "table2" as ew
    ON fw."X" = $1 AND ew.id = fw."Y";
  `, [param1]);

Upvotes: 0

Ming-Kai Ko
Ming-Kai Ko

Reputation: 9

I believe you can use .query to write your own query

Upvotes: 0

Gabriel Corrêa Diniz
Gabriel Corrêa Diniz

Reputation: 11

At least in the current version there is no way to do this feature (neither in the documentation nor in the web)

Upvotes: 1

Related Questions