• 简体中文

Querys

API Summary

1. CRUD API

Query	Add	Update	Delete
all
query		updateByQuery	deleteByQuery
queryPager
queryByCondition		updateByCondition	deleteByCondition
queryByField		updateByField	deleteByField
getById	add	update	deleteById
getByField

说明 ：

1. oor provide to constructor : Table and View

   Difference : View only include API of Query，Table include all API。

2. The API of last row ：getById / getByField / add / update /deleteById

   Only effect one effect, Others effect a list.

3. QuerySchema and Condition can use at query/update/delete

4. queryPager will return a struct of { total:number, list: <Type Entity>[] }

2. External API

This two api can use of custom function:

1. exec() : Equivalent to query (pg/mysql)， or search (es)
2. getClient() : Get the connection object, use to call some db apis.

QuerySchema

1. QuerySchema is a Object
2. QuerySchema construct of Fixed Properties and Custom Properties
3. Fixed Properties ends with _.
4. Custom Properties ends with MagicSuffix (or not with it).

Demo

User.query({
    start_: 0,
    count_: 20,
    order_: 'id',
    by_: 'desc',
    idMin: 200,                                 // id > 200
    idNot: 300,                                 // id != 300
    createDateMin : '2022-01-01',               // createDate > 2022-01-01
    createDateMax : new Date('2022-02-01'),     // createDate < 2022-02-01
    nameLike: 'title',                          // name like %title%
    name: 'oor'                                 // product = oor (No Suffix, No Magic )
})

Fix Properties

Property	Type	Description	Default
start_	number	Start Offset	0
count_	number	Limit Count	10
order_	string	Sort order field	id
by_	'asc' | 'desc'	Sory by method	desc
keyword_	string	Search KeyWord

Magic Suffix

QuerySchema can have any mount of Custom Properties.

Custom Properties = field name + Magic Suffix , no Magic Suffix eq to =.

'Min', 'MinThan', 'Max', 'MaxThan',                 // commom  > , >= , <  ,  <=
'MinH', 'MinD', 'MinM', 'MaxH', 'MaxD', 'MaxM',     // Only Date Hour / Day / Month
'Like', 'Likel', 'Liker',                           // Only String  like leftlike rightlike
'Bt', 'BtD', 'BtY', 'BtM',                          // BETWEEN, support Number/Date ,'BtY', 'BtM', 'BtD' Only  Spport Date
'Not',                                              // != or <>
'IsNull', 'NotNull',                                // isNull or Not NULL           This Suffix will avoid value
'>', '>=', '<', '<=', '=', '!=', '<>'               // Comparison Functions,
'In', 'NotIn'                                       // SQL IN Condition , use "," sperate items, Not support boolean date

Details List:

Suffix	Description	Support PG/MySql	Support ES
=	eq	All (default)	All (default)
Min , >	MoreThan	All	Number, Date
MinThan , >=	MoreThan or eq	String, Number, Date	Number, Date
Max , <	LessThan	All	Number, Date
MaxThan , <=	LessThan or eq	String, Number, Date	Number, Date
MinH	MoreThan or eq (Hour)	Date	Date
MinD	MoreThan or eq (Day)	Date	Date
MinM	MoreThan or eq (Month)	Date	Date
MaxH	LessThan or eq (Hour)	Date	Date
MaxD	LessThan or eq (Day)	Date	Date
MaxM	LessThan or eq (Month)	Date	Date
Like	Like	String	String
Likel	Starts With	String	String
Liker	Ends With	String	String
Bt	Between	Number, Date	Number, Date
BtD	Between (Day)	Date	Date
BtM	Between (Month)	Date	Date
BtY	Between (Year)	Date	Date
Not ,<>,!=	Not eq	String, Number, Boolean	String, Number, Boolean
IsNull	is Null	All	All
NotNull	is Not Null	All	All
In	in	String,Number	String,Number
NotIn	Not in	String,Number	String,Number

Bt , In , NotIn

The Bt Syntax like Notations for intervals :

Some Examples of Bt :

Demo	Description
{ fieldBt: '1' }	field >= 1
{ fieldBt: '[1' }	field > 1
{ fieldBt: '(1' }	field > 1
{ fieldBt: ',100' }	field <= 100
{ fieldBt: ',100]' }	field <= 100
{ fieldBt: ',100)' }	field < 100
{ fieldBt: '1,100' }	field >= 1 AND field <= 100
{ fieldBt: '[1,100)' }	field >= 1 AND field < 100
{ fieldBt: '(1,100)' }	field > 1 AND field < 100
{ fieldBt: '20201011,2022-10-11 11:11:11' }	field > {time-20201011} AND field < { time-2022-10-11 11:11:11}

In NotIn 语法 : In, NotIn 都是以逗号进行分割的字符串。

Condition

Notice : Condition is not suggest. Because condition will not validate.

But if need Build some complex query, you can use it .

1. Condition is a struct combined with any number of Item or Condition, and multi Levels.
2. Item is a struct of column (the database column name, not field name), and value , fn, fn is MagicSuffix.
3. Condition has a link descrie the relation of child items, support AND and OR

A demo of condition

const condition: WhereParam = {
    link: 'AND',
    items: [
        { column: 'a1', value: 'value1' },
        { column: 'a2', fn: '<', value: 'value2' },
        {
            link: 'OR', items: [
                { column: 'b1', fn: 'Like', value: 'value3' },
                {
                    link: 'NOT', items: [
                        { column: 'd1', value: 'test1' },
                        { column: 'd2', value: 'test2' },
                        {
                            link: 'AND', items: [
                                { column: 'e1', value: 'test6' },
                                { column: 'e2', value: 'test7' },
                            ]
                        }
                    ]
                },
                { column: 'b2', fn: '>=', value: 'value4' },
            ]
        },
        { column: 'a3', fn: '<', value: 'value1' },

    ]
}

await entity.queryByCondition(condition)