How to order on a relation field (foreign key) ?

[tonytoth]

I would like to find out if I am able somehow to order based on a relation field (foreign key) without creating a custom service. If I need to create a custom service, which would be the best way to do it ?

[tonytoth]

My question might be related to this 559 issue.

[tonytoth]

If someone is wondering how you can workaround this, this is my solution: resource.resolvers.ts

@Resolver(() => ResourceDTO)
export class ResourceResolver extends CRUDResolver(ResourceDTO, { pagingStrategy: PagingStrategies.OFFSET }) {
    constructor(
        @InjectQueryService(ResourceEntity) readonly service: QueryService<ResourceEntity>,
        private readonly resourcesService: ResourcesService
    ) {
        super(service);
    }

    @Mutation(() => ResourceDTO, { name: 'createOneResource' })
    async createOne(@Args('input', { type: () => NewResourceInput }) input): Promise<ResourceDTO> {
        return await this.resourcesService.create(input.input);
    }

    @UseGuards(AuthGuard, OurACGuard)
    @Query(() => [ResourceDTO], { name: 'resources' })
    async queryMany(@Args() query: ResourceQuery): Promise<ArrayConnectionType<ResourceDTO>> {
        const filter: Filter<ResourceDTO> = mergeFilter(query.filter ?? {}, {});
        const completedQuery = mergeQuery(query, { filter });

        return ResourceConnection.createFromPromise(
            (q) => this.resourcesService.query(q),
            completedQuery,
            (q) => this.service.count(q),
        );
    }
}

resources.service.ts

import { HttpException, HttpStatus, Inject, Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { ResourceEntity } from './resource.entity';
import { ProductsService } from '../products/products.service';
import { UpdateResourceInput } from './input/update-resource.input';
import { NewResourceInput } from './input/new-resource.input';
import { ResourceQuery } from './types';
import { FilterQueryBuilder } from '@nestjs-query/query-typeorm/dist/src/query';
import { ResourceSortSectionEntity } from './resourceSortSection/resourceSortSection.entity';
import { Unit } from '../units/unit.entity';
import { QueryService } from '@nestjs-query/core';
import { ResourceDTO } from './dto/resource.dto';
import { TypeOrmQueryService } from '@nestjs-query/query-typeorm';

export interface ResourceOutput {
  items?: ResourceEntity[];
  totalCount?: number;
}

@Injectable()
@QueryService(ResourceDTO)
export class ResourcesService extends TypeOrmQueryService<ResourceEntity> {
  constructor(
    @Inject(ProductsService) private readonly productService,
    @InjectRepository(ResourceEntity)
    private readonly resourceRepository: Repository<ResourceEntity>,
  ) {
    super(resourceRepository);
  }
  async query(queryArgs: ResourceQuery) {
    const filterQueryBuilder = new FilterQueryBuilder<ResourceEntity>(this.resourceRepository);
    const querySelector = filterQueryBuilder.select(queryArgs);

    querySelector.leftJoinAndSelect(ResourceSortSectionEntity, "resource_sort_section", "resource_sort_section.id = ResourceEntity.sectionId")
    .leftJoinAndSelect(Unit, "unit", "unit.id = ResourceEntity.unitId");

    if(!queryArgs.sorting || queryArgs.sorting) { 
      querySelector.orderBy({
        'unit.sortNo': 'ASC',
        'resource_sort_section.sortNo': 'ASC',
        'ResourceEntity.sortNo': 'ASC',
      });
    }
    return querySelector.getMany();
  }
}

P.S: You might want to add ResourceResolver in your module in exports, providers array.

[tonytoth]

@doug-martin I would like to hear your opinion on this workaround. Thank you !

[thehappycoder]

Another workaround is to use database view + ORM model on top of this view.