How to order on a relation field (foreign key) ?
Opened this issue · 4 comments
tonytoth commented
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 commented
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 commented
@doug-martin I would like to hear your opinion on this workaround.
Thank you !
thehappycoder commented
Another workaround is to use database view + ORM model on top of this view.