loopback4-query
v1.0.0
Published
An enhanced query for loopback-next, which supports cascading queries through relational conditions.
Downloads
644
Maintainers
Readme
loopback4-query
An enhanced query for loopback-next, which supports cascading queries through relational conditions.
Features
- Facilitates cascading filter searches using
where
clauses (e.g.,{where: {'relation_ab.relation_bc.relation_cd.attribute': 'specificValue'}}
). - Ensures full compatibility with loopback-next's Where Filter.
- Provides support for various relations including hasMany, belongsTo, hasOne, and hasManyThrough.
- Incorporates Polymorphic Relations.
- Supports multiple relational databases like
PostgreSQL
,MSSQL
,MySQL
,MariaDB
,SQLite3
, andOracle
. For unsupported databases, Mixin The Repository hands off to the native query method. - The access and
findOne
objects' loading loaded event isn't supported.
Install
npm:
npm install loopback4-query
Yarn:
yarn add loopback4-query
Getting Started
import {DefaultQuery, Query} from 'loopback4-query';
class SomeClass {
query: Query<SomeEntity>;
constructor(
@repository(OrgRepository)
public orgRepository: OrgRepository,
) {
this.query = new DefaultQuery(this.orgRepository);
}
async findSomeEntity() {
// Find all `users` that have access to `project` and include `projects` with `bleco` in their name.
// CAUTION: `projects.name` condition must be the same as include `projects` condition.
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 with the name condition of projects.
{
relation: 'projects',
scope: {
where: {
name: 'bleco',
},
},
},
],
});
}
}
DefaultQuery Overview
DefaultQuery facilitates model search queries utilizing relational cascading criteria.
How To Use
Initialization
You can instantiate DefaultQuery with a Repository instance, supporting include clause via repository inclusion resolvers:
new DefaultQuery(repository);
Alternatively, you can use a model class and a datasource instance. Note that the include clause isn't supported here:
new DefaultQuery(entityModel, datasourceInstance);
Inheriting QueryRepositoryMixin
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 classoptions: boolean | QueryMixinOptions
: mixin optionsoverrideCruds
: whether to override native CRUD methods, the default isfalse
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);
}
}
Using the @mixinQuery
decorator
Syntax:
@mixinQuery(options: boolean | QueryMixinOptions = false)
parameter:
options: boolean | QueryMixinOptions
: mixin optionsoverrideCruds
: whether to override native CRUD methods, the default isfalse
@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> {}
Applying the @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 'loopback4-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 'loopback4-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
likeINNER JOIN
{ where: { 'relation_a.relation_b.id': {neq: null}, }, }
Use
$join
for relational queries (usingLEFT 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 fieldsComparing two values:
{ where: { $expr: { eq: [1, 0], }, }, }
Field to value:
{ where: { $expr: { eq: ['$joination_a.relation_b.property', 'some value'], }, }, }
Value to field:
{ where: { $expr: { eq: ['some value', '$joination_a.relation_b.property'], }, }, }
Field to 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 toorganizations
withbleco
in their name:
const userQuery = new DefaultQuery(userRepository);
const users = await userQuery.find({
where: {
'orgs.name': {
like: '%bleco%',
},
},
});
- Find all
users
that have access toprojects
withbleco
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