TypeORM Sort by subset of one to many relation

Question

I have the following structure representing rows of a user created table with dynamic columns:

@Entity()
class Row {
  @PrimaryGeneratedColumn()
  id: number;

  @OneToMany(() => RowValue, (item) => item.row)
  rowValues: RowValue[];
}

And the row values:

@Entity()
export class RowValue {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  key: string;

  @Column('simple-json')
  value: string;

  @ManyToOne(() => Row, (row) => row.rowValues)
  row: Row;
}

Now I would like to allow my users to sort the Row list by a specific row value.

I know that the order option supports nesting. But since in my case it is a subset of the relation I am not sure how to implement it, even with the query builder.

Answer 1

I implemented this using the query builder with a subquery statement:

const sortBy = 'SomeColumn';
const sortDirection = 'DESC';
const limit = 10;
const offset = 0;


const repository = this.connection.getRepository(Row);

const sortByQuery = repository
      .createQueryBuilder('sorted_results')
      .innerJoinAndSelect('sorted_results.rowValues', 'rowValues')
      .where('rowValues.key = :columnName', { columnName: sortColumn })
      .orderBy('rowValues.value', sortDirection)
      .limit(limit)
      .offset(offset);

const query = repository
      .createQueryBuilder('row')
      .innerJoinAndSelect('row.rowValues', 'rowValues')
      .innerJoin(
        `(${sortByColumn.getQuery()})`,
        'sorted_results',
        'sorted_results.sorted_results_id = row.id'
      )
      .orderBy('sorted_results.rowValues_value', sortDirection)
      .setParameters(sortByQuery.getParameters());

const results = await query.getMany();