Loopback 4: Many to Many Relation

Question

I am trying to implement a way to filter data in tables which have ManytoMany relationship.

I have following tables job, job_category and category.

So far I am thinking in do a query to job_category using job_id and then use that result to add a condition using IN() but I do not find any way to impĺement this option either.

Questions:

1. How to implement ManytoMany relation in Loopback 4?

2. How to filter a query using IN?

PD I can use $inq for question number 2.

filter.where = {
   ...filter.where,
   id: {inq: [2, 7]},
};

Answer 1

Taking the context of your question, a many-to-many relationship can be implemented in lb4 as below.

The jobs model (a sample) -

    @model({
      name: 'jobs',
    })
    export class Job extends Entity {
      @property({
        type: 'number',
        id: true,
      })
      id: number;

      @property({
        type: 'string',
        required: true,
      })
      name: string;

      // Other columns of the table.....

      constructor(data?: Partial<Job>) {
        super(data);
      }
    }

The categories model (a sample) -

    @model({
      name: 'categories',
    })
    export class Category extends Entity {
      @property({
        type: 'number',
        id: true,
      })
      id: number;

      @property({
        type: 'string',
        required: true,
      })
      name: string;

      // Other columns of the table.....

      constructor(data?: Partial<Category>) {
        super(data);
      }
    }

In the Job categories relationship model, we are going to implement the belongs to relationship with both Job and Category Models. This will ensure m:n relationship.

    @model({
      name: 'job_categories',
    })
    export class JobCategory extends Entity {
      @property({
        type: 'number',
        id: true,
      })
      id: number;

      @belongsTo(() => Job)
      job_id: number;

      @belongsTo(() => Category)
      category_id: number;

      constructor(data?: Partial<JobCategory>) {
        super(data);
      }
    }

Now, using lb4 CLI, you can create a repository and REST controller for job categories model and use the find methods there to fetch data. Unfortunately, includes parameter in Filter class for find methods is not yet implemented in lb4. Its still WIP. Refer this thread from loopback-next repo for updates. Until then, you may have to add custom logic t controller or repository classes to achieve this. Two suggested approaches from my side are below.

1. Configure belongs to relation in repository (Refer documentation here) and use it inside controller to fetch are respond with related data (Refer implementation here). You may need to create your own response model for this. We created our own DTO for this purpose. You can also return 'any' type as response for this but that's not recommended.
2. You can execute your own join query if needed. That's native query approach. But, unfortunately again execute function in repository class is not implemented yet. See here. Its available in dts though. So, we implemented a work around until its implemented. We created a Base repository class which will be inherited by all repository classes in our application (replacing all extends DefaultCrudRepository with extends AppDefaultCrudRepository). Here is the implementation for base repository.

    export abstract class AppDefaultCrudRepository<
      T extends Entity,
      ID
    > extends DefaultCrudRepository<T, ID> {
      constructor(
        entityClass: typeof Entity & {
          prototype: T;
        },
        dataSource: AppDataSource,
      ) {
        super(entityClass, dataSource);
      }

      execute(
        command: Command,
        parameters: NamedParameters | PositionalParameters,
        options?: Options,
      ): Promise<AnyObject> {
        // Commented below statement until it is implemented in lb4
        // return super.execute(command, parameters, options);
        return this.dataSource.execute(command, parameters, options);
      }
    }

Hope this helps with your question #1. For question #2, you already mentioned the approach. That works.

Answer 2

You can implement a many-to-many relationship in Loopback 4 using the hasManyThrough relationship. The hasManyThrough relationship is an extension to the hasMany relationship.

Currently, this feature is a pull request waiting to be accepted.

https://github.com/strongloop/loopback-next/pull/2359

However, the code for this pull request has been packaged and can be installed and used the following way.

npm install --save @loopback/repository@git+https://[e-mail]/codejamninja/loopback-next.git#npm/codejamninja/has-many-through-using-has-many@1.11.0-rc.1

models/patient.model.ts

import { Entity, model, property, hasMany } from '@loopback/repository';
import { Appointment, Patient } from '../models';

@model()
export class Physician extends Entity {
  @property({
    type: 'string',
    id: true
  })
  id?: string;

  @hasMany(() => Patient, { through: () => Appointment })
  patients: Patient[];
}

repositories/patient.repository.ts

import {
  DefaultCrudRepository,
  HasManyThroughRepositoryFactory,
  repository
} from '@loopback/repository';
import { inject, Getter } from '@loopback/core';
import { MemoryDataSource } from '../datasources';
import { Patient, Physician } from '../models';
import { AppointmentRepository, PhysicianRepository } from '../repositories';

export class PatientRepository extends DefaultCrudRepository<
  Patient,
  typeof Patient.prototype.id
> {
  public readonly physicians: HasManyThroughRepositoryFactory<
    Physician,
    typeof Patient.prototype.id
  >;

  constructor(
    @inject('datasources.memory')
    dataSource: MemoryDataSource,
    @repository.getter('AppointmentRepository')
    getAppointmentRepository: Getter<AppointmentRepository>,
    @repository.getter('PhysicianRepository')
    getPhysicianRepository: Getter<PhysicianRepository>
  ) {
    super(Patient, dataSource);
    this.physicians = this.createHasManyThroughRepositoryFactoryFor(
      'physicians',
      getPhysicianRepository,
      getAppointmentRepository // notice the through repository getter
    );
  }
}

There is a basic example of this at the following link.

https://github.com/codejamninja/medical-practice-api

Please note that this api may change before the pull request is accepted.

You can read more about how this relationship works at the following links.

https://loopback.io/doc/en/lb3/HasManyThrough-relations.html https://guides.rubyonrails.org/association_basics.html#the-has-many-through-association