@bleco/query NPM

@bleco/query

A loopback-next sql query based on Kenx that supports LEFT JOIN

Features

Support cascading filter queries (through where clauses such as {where: {'relation_ab.relation_bc.relation_cd.property': 'value'}})
Fully compatible with loopback-next's Where Filter
Support hasMany, belongsTo, hasOne and hasManyThrough Relation
Support Polymorphic Relation
Support PostgreSQL, MSSQL, MySQL, MariaDB, SQLite3, Oracle relational databases, other databases, extended by Mixin The Repository will delegate to the parent's native query method.
access and findOne loading objects are not supported loaded event.

Install

npm:

npm install @bleco/query

Yarn:

yarn add @bleco/query

Getting Started

import {DefaultQuery, Query} from '@bleco/query';

import {typequery} from './decorators';

class SomeClass {
  query: Query<SomeEntity>;

  constructor(
    @repository(OrgRepository)
    public orgRepository: OrgRepository,
  ) {
    this.query = new DefaultQuery(this.orgRepository);
  }

  async findSomeEntity() {
    return this.query.find({
      where: {
        // Through the name condition of projects, cascade query Org. But the result does not contain the associated object projects. To include associated objects, use the include method.
        'projects.name': 'bleco',
        age: {
          gt: 10,
          lt: 20,
        },
      },
      include: [
        // Contains the associated object projects
        {
          relation: 'projects',
          scope: {
            where: {
              name: 'bleco',
            },
          },
        },
      ],
    });
  }
}

DefaultQuery

A query that performs model filtering queries through relational cascading conditions.

Usage

Construct

Construct DefaultQuery with a Repository instance parameter, support include clause with repository inclusion resolvers

new DefaultQuery(repository);

Construct DefaultQuery with a model class and a datasource instance parameters, include is not supported clause

new DefaultQuery(entityClass, datasource);

`QueryRepositoryMixin` inheritance

Extends native find and findOne support for seamless cascading queries by mixing in Repository with QueryRepositoryMixin. (Note: find is not supported and findOne's access and loaded event)

method:

declare function QueryRepositoryMixin<
  M extends Entity,
  ID,
  Relations extends object,
  R extends MixinTarget<EntityCrudRepository<M, ID, Relations>>,
>(superClass: R, options: boolean | QueryMixinOptions = {});

parameter:

superClass: the inherited class
options: boolean | QueryMixinOptions: mixin options
- overrideCruds: whether to override native CRUD methods, the default is false

export class FooRepository
  extends QueryRepositoryMixin<
    Foo,
    typeof Foo.prototype.id,
    FooRelations,
    Constructor<DefaultCrudRepository<Foo, typeof Foo.prototype.id, FooRelations>>
  >(DefaultCrudRepository, {overrideCruds: true})
  implements DefaultCrudRepository<Foo, typeof Foo.prototype.id, FooRelations>
{
  constructor(dataSource: juggler.DataSource) {
    super(Foo, dataSource);
  }
}

`@mixinQuery` decorator

Syntax:

@mixinQuery(options: boolean | QueryMixinOptions = false)

parameter:

options: boolean | QueryMixinOptions: mixin options
- overrideCruds: whether to override native CRUD methods, the default is false

@mixinQuery(true)
export class FooRepositoryWithQueryDecorated extends DefaultCrudRepository<Foo, typeof Foo.prototype.id> {
  constructor(dataSource: juggler.DataSource) {
    super(Foo, dataSource);
  }
}

export interface FooRepositoryWithQueryDecorated extends QueryRepository<Foo> {}

`@query` decorator

Syntax:

@query(modelOrRepo: string | Class<Repository<Model>> | typeof Entity, dataSource?: string | juggler.DataSource)

The @query decorator creates a new query instance by injecting an existing repository instance, or from a model and datasource.

Create a query instance in a controller, you can first define model and datasource, then import into controller, and use @query to inject

import {Query, query} from '@bleco/query';
import {repository} from '@loopback/repository';

import {db} from '../datasources/db.datasource';
import {Todo} from '../models';

export class TodoController {
  @query(Todo, db)
  todoQuery: Query<Todo>;
  // ...
}

If model or datasource are already bound to app, they can be created by passing their names directly to the @query injector, as follows:

// with `db` and `Todo` already defined.
app.bind('datasources.db').to(db);
app.bind('models.Todo').to(Todo);

export class TodoController {
  @query('Todo', 'db')
  todoQuery: Query<Todo>;
  //etc
}

`QueryEnhancedCrudRepository` inherits from `DefaultCrudRepository` and implements `mixinQuery`

DefaultCrudRepository is the default CRUD interface implementation of loopback, which has all the functions of the CRUD interface. Most business repositories inherit from it.

Here we provide a class that inherits from DefaultCrudRepository and replaces QueryEnhancedCrudRepository of mixinQuery with Query replaces find, findOne and count native queries. For data sources that are not yet supported (such as non-relational databases), they will be passed directly to the native query.

Patching

For historical projects, it is not convenient to use Mixin or inheritance for refactoring. Therefore, we provide a Patching scheme that can be initialized in the application, not yet patching the DefaultCrudRepository before loading.

import {queryPatch} from '@bleco/query';
import {DefaultCrudRepository} from '@loopback/repository';

export async function main(options: ApplicationConfig = {}) {
  // patching `DefaultCrudRepository`
  queryPatch(DefaultCrudRepository);

  const app = new TodoListApplication(options);
  await app.boot();
  await app.start();

  const url = app.restServer.url;
  console.log(`Server is running at ${url}`);
  return app;
}

`queryPatch(repoClass)`: Patching a `Repository` class or instance

// patching a repository class
queryPatch(DefaultCrudRepository);

// patching a repository instance
queryPatch(repository);

// or patching self
class MyRepository extends DefaultCrudRepository<MyModel, typeof MyModel.prototype.id> {
  constructor(dataSource: juggler.DataSource) {
    super(MyModel, dataSource);
    queryPatch(this);
  }
}

Query API

export interface Query<T extends Entity, Relations extends object = {}> {
  entityClass: EntityClass<T>;

  /**
   * Find matching records
   *
   * @param filter - Query filter
   * @param options - Options for the operations
   * @returns A promise of an array of records found
   */
  find(filter?: QueryFilter<T>, options?: Options): Promise<(T & Relations)[]>;

  /**
   * Find one record that matches filter specification. Same as find, but limited to one result; Returns object, not collection.
   *
   * @param filter - Query filter
   * @param options - Options for the operations
   * @returns A promise of a record found
   */
  findOne(filter?: QueryFilter<T>, options?: Options): Promise<(T & Relations) | null>;

  /**
   * Count matching records
   * @param where - Matching criteria
   * @param options - Options for the operations
   * @returns A promise of number of records matched
   */
  count(where?: QueryWhere<T>, options?: Options): Promise<{count: number}>;
}

QueryFilter

Compatible with loopback native Filter. Extended support for cascading paths as where children query condition.

query with LEFT JOIN

{
  where: {
    'relation_a.relation_b.property': 'some value',
  },
}

query with LEFT JOIN like INNER JOIN

{
  where: {
    'relation_a.relation_b.id': {neq: null},
  },
}

Use $join for relational queries (using LEFT JOIN)

{
  where: {
    $join: 'relation_a.relation_b',
  },
}

Or define multiple relationships at the same time

{
  where: {
    $join: ['relation_a.relation_b', 'relation_c.relation_d'],
  },
}

Use $expr for filtering queries between fields -value <-> value:

{
  where: {
    $expr: {
      eq: [1, 0],
    },
  },
}

Field <-> Value:

{
  where: {
    $expr: {
      eq: ['$joination_a.relation_b.property', 'some value'],
    },
  },
}

Value <-> fields:

{
  where: {
    $expr: {
      eq: ['some value', '$joination_a.relation_b.property'],
    },
  },
}

field <-> field:

{
  where: {
    $expr: {
      eq: ['$joination_a.relation_b.property', '$joination_c.relation_d.property'],
    },
  },
}

Polymorphic Relations Query. For details, please refer to the relevant Test Case.
```
{
  where: {
    'deliverables(Letter).property': 'some value',
  },
}
```

For example, there are the following models:

// user.model.ts
@model()
export class User extends Entity {
  @property({
    type: 'number',
    id: true,
    generated: true,
  })
  id?: number;

  @property({
    type: 'string',
  })
  email?: string;

  @hasMany(() => Org, {through: {model: () => OrgUser}})
  orgs: Org[];

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

// org.model.ts
@model()
export class Org extends Entity {
  @property({
    type: 'number',
    id: true,
    generated: true,
  })
  id: number;

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

  @hasMany(() => User, {through: {model: () => OrgUser}})
  users: User[];

  @hasMany(() => Proj, {keyTo: 'org_id'})
  projs: Proj[];

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

// proj.model.ts
@model()
export class Proj extends Entity {
  @property({
    type: 'number',
    id: true,
    generated: true,
  })
  id: number;

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

  @property({
    type: 'string',
  })
  title?: string;

  @belongsTo(() => Org, {name: 'org'})
  org_id?: number;

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

Find all users that have access to organizations with bleco in their name:

const userQuery = new DefaultQuery(userRepository);

const users = await userQuery.find({
  where: {
    'orgs.name': {
      like: '%bleco%',
    },
  },
});

Find all users that have access to projects with bleco in their name:

const userQuery = new DefaultQuery(userRepository);

const users = await userQuery.find({
  where: {
    'orgs.projs.title': {
      like: '%bleco%',
    },
  },
});

Thanks

knex-filter-loopback: Declarative filtering for knex.js based on the Loopback Where Filter.
loopback-connector-postgresql: supports LEFT JOIN only across one postgres datasource
loopback-connector-postgresql-include: Resolving Include filter with left join