Query multiple level of nesting relations in filtering

[MariusParasca]

📚 Documentation

Hi @doug-martin , thanks a lot for this incredibly useful library!

I stumble against one issue here. I didn't find any documentation for it yet and I'm not sure if is possible or not to achieve this
Is it possible to based my filtering using nested relations (2,3, or more)?
Please watch the example below for a better understanding of my question

For example:
I have the following database relation:

Is there possible to query the appointment like this (query appointments based on user's id having the user in relation with the client and obviously client in relation with appointment):

query appointments {
   appointments(filter: { client: { user: {id: { eq:"49082d78-c63c-4f2e-82e1-601d7ee5d6fe"}} }}) 
    { ... }
}

I can only query an appointment using the client id:

query appointments {
  appointments(filter: { client: { id: { eq:"49082d78-c63c-4f2e-82e1-601d7ee5d6fe"} }})
  { ... }
}

Here is the code that I built so far:

appointemnt.model.ts

import {
  FilterableField,
  FilterableRelation,
} from '@nestjs-query/query-graphql';
import { ID, ObjectType } from '@nestjs/graphql';
import { ClientModel } from 'src/client/dto/client.model';
import { EmployeeModel } from 'src/employee/dto/employee.model';

@ObjectType('Appointment')
@FilterableRelation('client', () => ClientModel, {
  disableRemove: true,
  disableUpdate: true,
})
@FilterableRelation('employee', () => EmployeeModel, {
  disableRemove: true,
  disableUpdate: true,
})
export class AppointmentModel {
  @FilterableField(() => ID)
  id: string;

  @FilterableField()
  customClientPhone: string;

  @FilterableField()
  customClientName: string;

  @FilterableField()
  createdDateTime: Date;

  @FilterableField()
  startDateTime: string;

  @FilterableField()
  endDateTime: string;

  @FilterableField()
  canceledDateTime: string;

  @FilterableField()
  canceledReason: string;
}

client.model.ts

import {
  FilterableField,
  FilterableRelation,
} from '@nestjs-query/query-graphql';
import { Field, ID, ObjectType } from '@nestjs/graphql';
import { UserModel } from 'src/user/dto/user.model';

@ObjectType('Client')
@FilterableRelation('user', () => UserModel, {
  disableRemove: true,
  disableUpdate: true,
})
export class ClientModel {
  @FilterableField(() => ID)
  id: string;
}

user.model.ts

import { FilterableField } from '@nestjs-query/query-graphql';
import { Field, ID, ObjectType } from '@nestjs/graphql';

@ObjectType('User')
export class UserModel {
  @FilterableField(() => ID)
  id: string;

  @FilterableField()
  email: string;

  @FilterableField()
  firstName: string;

  @FilterableField({ nullable: true })
  lastName: string;

  @FilterableField()
  phone: string;

  @Field({ nullable: true })
  avatarLink: string;
}

Is it possible to query appointments based on the client's user model as described in the graphql query above?

[vladmelnyk]

is there a hackish solution we can use until you release a proper one?
@doug-martin

[smolinari]

Don't mean to sound smarts-ass-ish, but you could maybe create your own resolver? 😀 appointmentsByUserId?

Scott

[MariusParasca]

@smolinari I could of, but it was nice if what I asked was possible :D

[lavaxun]

I'm also encountering the same issue as you.
May I know what was your solution / workaround for this?
@MariusParasca

[psteinroe]

I guess this is related to #1041.

We will have to extend the query services of all packages first by allowing "recursive" relation filters. Only then, we can add this to the GraphQL layer (which should be pretty straightforward as commented by @doug-martin).

Is this already worked on @doug-martin?

Otherwise, I can have a look into TypeORM, but don't have experience with any of the other packages. Any takers?

• mongoose
• sequelize
• typegoose
• TypeORM @steinroe

[aceonics-team]

As per @steinroe's comment it seems @nestjs-query/query-typeorm@0.30.0 supports recursive filters however other ORMs does not. This may take time for all ORMs to support this.

However, can we use this feature in @nestjs-query/query-graphql if @nestjs-query/query-typeorm is used?
It would allow many users like me to use deep filtering in graphql queries.

Currently due to this limitation I have to create views in typeorm so that I can provide filter for nested relations from UI on entity table. Also I was wondering if we can sort by relation's field as well as nested relation's field.

[Flusinerd]

Any updates on this?
I'd be interested as well. I'm also using TypeORM.

[s4kh]

@doug-martin any update on this? If the project is using TypeORM can we use this somehow?

[mrnmilutinovic]

@s4kh

I am having the same problem and, the solution for this is a bit of a workaround until @doug-martin comes back.
What I ended up doing is as follows:

1. Create a virtual relation on your DTO as the property is there
2. Create a service that will extend the RelationQueryService
3. Override the query method of that service and map the filter property to the actual nested filter. We know that works based on the work @psteinroe did here
4. Inject your service in your module

Here is an example of that:
My situation is that I have an Inquiry entity that references the ClientMetadatum entity, which has a reference to the User entity, and that is where FirstName and LastName are. I want to filter Inquiries FirstName and the LastName from the nested User entity. It is pretty much the exact situation @MariusParasca described when opening this ticket.

The main DTO

@ObjectType('Inquiry')
@KeySet(['id'])
@QueryOptions({ pagingStrategy: PagingStrategies.OFFSET })
@FilterableRelation('clientMetadatum', () => ClientMetadatumDto, { disableRemove: true, nullable: true })
@FilterableRelation('user', () => UserDto, { disableRemove: true, disableUpdate: true, nullable: true })
export class InquiryDto {...}

InquiriesQueryService

@Injectable()
export class InquiriesQueryService extends RelationQueryService<Inquiry> {
  constructor(
    @InjectQueryService(Inquiry) queryService: QueryService<Inquiry>,
  ) {
    super(queryService, {});
  }

  remapUserFilter(query: Query<Inquiry & { user: any }>) {
    const userFilter = query.filter.user;
    if (userFilter) {
      return {
        clientMetadatum: {
          user: userFilter,
        },
      };
    }
    return {};
  }

  query(query: Query<Inquiry & { user: any }>): Promise<Inquiry[]> {
    const filter = getFilterOmitting(query.filter, 'user');
    /**
     * We know that below works based on this PR being merged:
     * https://github.com/doug-martin/nestjs-query/pull/1370/files
     */

    const mappedQuery = {
      ...query,
      filter: {
        ...filter,
        ...this.remapUserFilter(query),
      },
    };
    return super.query(mappedQuery as any);
  }
}

Injecting the service

NestjsQueryGraphQLModule.forFeature({
      imports: [
        NestjsQueryTypeOrmModule.forFeature([
          ...
        ]),
      ],
      services: [InquiriesQueryService],
      resolvers: [
        {
          DTOClass: InquiryDto,
          EntityClass: Inquiry,
          ServiceClass: InquiriesQueryService,
        },
        ...
      ],
    }),

What we did here is take the original query that comes from the client and remap it to something we know works against the underlying nestjs-query/typeorm package.

So from:

{
  "filter": {
    "user": {
      "firstName": {
        "iLike": "%Nik%"
      }
    }
  }
}

We went to this:

{
  "filter": {
    "clientMetadatum": {
      "user": {
        "firstName": {
          "iLike": "%Nik%"
        }
      }
   }
  }
}

And then we let the @psteinroe PR work its magic :)

I hope this helps.