Order required collection with typeOrm in the Nest.js application

Question

I have two little entities TodoEntity and CategoryEntity. It creates the tables that I expect in the database. I use Postgres. And I need to select from the database categories ordered by actual date. I can select all categories using QueryBuilder. But the problem is I don't need the categories to repeat. How to make them distinct?

      .createQueryBuilder('todo')
      .select('category')
      .innerJoinAndSelect('todo.category', 'category')
      .orderBy('todo.actualTime', 'ASC')
      .getRawMany();

@Entity({ name: 'todo_entity' })
class TodoEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ name: 'name' })
  name: string;

  @Column({ name: 'description' })
  description: string;

  @Column({ name: 'actual_time', type: 'timestamp' })
  actualTime: Date;

  @ManyToOne(() => CategoryEntity, (categoryEntity) => categoryEntity.id, {
    cascade: true,
  })
  category: CategoryEntity;
}


@Entity({ name: 'category_entity' })
class CategoryEntity {
  @PrimaryGeneratedColumn({ name: 'id' })
  id: number;

  @Column({ name: 'title' })
  @Index({ unique: true })
  title: string;
}

Answer 1

I am guessing you need to order the categories by the most recent actual_time of the linked todo entities.

First, you need to setup the relationship of category_entity with todo_entity on CategoryEntity class.

@Entity({ name: 'category_entity' })
class CategoryEntity {
  @PrimaryGeneratedColumn({ name: 'id' })
  id: number;

  @Column({ name: 'title' })
  @Index({ unique: true })
  title: string;

  @OneToMany(type => TodoEntity, (todos) => todos.category)
  todos: TodoEntity[];
}

Then you can use the query builder in category repository to build the query as follows,

.createQueryBuilder('category')
.leftJoinAndSelect(
  (qb) => qb.from(TodoEntity, 'todo')
    .select('MAX("actual_time")', 'actual_time')
    .addSelect('"categoryId"', 'category_id')
    // Add the columns you want from `TodoEntity`
    .addSelect('description')
    .groupBy('category_id'),
  'last_todo',
  'last_todo.category_id = category.id',
)
// Remove the following line if you need all the columns or update it based on the columns you need
.select(['category_entity.id', 'category_entity.title', 'last_todo.actual_time', 'last_todo.description'])
.orderBy('last_todo.actual_time', 'DESC')
.getRawMany();

If you're wondering how categoryId appeared in the query, it is an autogenerated column by typeorm for todo_entity table since we specify the foreign key relationship.

This should generate the following Postgres query,

SELECT category_entity.id AS category_entity_id, category_entity.title AS "category_entity_title", last_todo.actual_time, last_todo.description
FROM "category_entity" "category"
LEFT JOIN (
    SELECT MAX("actual_time") AS "actual_time", "categoryId" AS "category_id", "description"
    FROM "todo_entity" "todo"
    GROUP BY category_id
) last_todo
ON last_todo.category_id = category.id
ORDER BY last_todo.actual_time DESC;

If you need to select all the columns of TodoEntity, then you will need to do another left join on TodoEntity after the current left join.

Cheers! 🍻