Collection of helper functions for database crud filtering
- Source:
- Copyright:
- 5/7/19
Methods
(inner) buildMongooseQueryFromReq(filterOptions, schema, req, jrResult)
- Source:
Build a mongoose (mongo?) query to filter only certain rows of a database table, from form variables in a user request. This is used to allow the crud admin tables to be filtered by an admin user using simple column filters. This function includes sort fields and direction, and pagination values (offset, size)
Parameters:
| Name | Type | Description |
|---|---|---|
filterOptions |
* | defined default sort field/direction, default page size, max page size, etc. |
schema |
* | defines the column headers available and their characteristics |
req |
* | express request |
jrResult |
* | a JrResult object that we will push error codes into in the case of error |
Returns:
tuple { query, queryOptions, queryUrlData } where queryUrlData is an object with data to include in form data/links to reproduce the request (useful for going to next page, etc)
(private, inner) convertReqQueryStringToAMongooseFindFilter(fkey, fieldSchema, querystr, jrResult)
- Source:
Take a query string (which is in an adhoc format described below) and convert it into a safe mongoose(mongo) query object that can be sent to the database
Notes
format for query string is a bit ad hoc, but works like this:
the search phrase is first divided into a set of OR tests by splitting using the text " or " (without quotes)
alternatively a comma may be used in place of " or "
with each OR phrase we can have a set of AND queries, separated by " and " (without quotes)
integer (Number) fields are simple integers\
Operators for numbers are < > <= >= = == != !== !< !> !<= !>=
So some example valid numeric searches:
<20
<20 and >5
<20 or >100
<20 and >5 or >100 [remember this is treated like (<20 and >5) or (>100) ]\
Date fields are exactly like numbers, EXCEPT that the numeric values in the search query are treated as dates, X number of days in the past
So <5 means the date is less (older) than 5 days ago\
String fields only support the operators = == != !==
But string queries are parsed slightly specially.
First, strings in double quotes are tested for exact matches, they do NOT do a substring %LIKE% match
Strings not in double quotes are searched for as substrings in the field as if they were %LIKE% matches in sql
Strings enclosed in // are treated as regular expression searches
Note that the and/or operators are not smart about being in quotes, which means that you simply CANNOT search for something with " and " or " or "" in it or commas\
Additionally you can use the constant "null" or "undefined" (not in quotes) to search for undefined value, or !null to search for values that are NOT undefined or null\
Parameters:
| Name | Type | Description |
|---|---|---|
fkey |
string | the name of the column (field) |
fieldSchema |
object | the schema definition for the field |
querystr |
string | the string specified by the user specifying how to filter the data |
jrResult |
object | errors will be pushed into this object |
Returns:
the query object generated
(private, inner) convertReqQueryStringToAMongooseFindFilterBoolean(fkey, schemaType, querystr, jrResult)
- Source:
- See:
-
- convertReqQueryStringToAMongooseFindFilter
A boolean-field-specific version of the function that makes a query object from a query string
Parameters:
| Name | Type | Description |
|---|---|---|
fkey |
* | |
schemaType |
* | |
querystr |
* | |
jrResult |
* |
Returns:
query object
(inner) convertReqQueryStringToAMongooseFindFilterGenericOperator(fkey, schemaType, querystr, operators, opChars, valPat, mongoValFunc, standaloneOpString, jrResult)
- Source:
Parse a query string using and and or separators and create a query object with and and or arrays
Parameters:
| Name | Type | Description |
|---|---|---|
fkey |
string | |
schemaType |
* | |
querystr |
string | |
operators |
array |
|
opChars |
* | |
valPat |
* | |
mongoValFunc |
* | |
standaloneOpString |
* | |
jrResult |
* |
Returns:
an array of disjunctive queries (ors)
(inner) convertReqQueryStringToAMongooseFindFilterGenericOperatorResolveVal(jrResult, fkey, opVal, mongoOp, mongoValFunc)
- Source:
Examine a string value (opVal) and operator, and handle null|undefined case specially
Parameters:
| Name | Type | Description |
|---|---|---|
jrResult |
JrResult | |
fkey |
* | |
opVal |
* | |
mongoOp |
* | |
mongoValFunc |
* |
Returns:
an object, either simple value or operator and value for handling null/undefined cases
(private, inner) convertReqQueryStringToAMongooseFindFilterMongoStrCmp(strVal, jrResult)
- Source:
Take a query string for a string field, and make a query object out of it which will be either a regex style query to act like a wildcard match, or pass through the regex search if user specifies it as /regex/
Parameters:
| Name | Type | Description |
|---|---|---|
strVal |
string | |
jrResult |
object |
Returns:
query object
(private, inner) convertReqQueryStringToAMongooseFindFilterNumeric(fkey, schemaType, querystr, subType, jrResult)
- Source:
- See:
-
- convertReqQueryStringToAMongooseFindFilter
A numeric-field-specific version of the function that makes a query object from a query string
Parameters:
| Name | Type | Description |
|---|---|---|
fkey |
* | |
schemaType |
* | |
querystr |
* | |
subType |
* | |
jrResult |
* |
Returns:
query object
(private, inner) convertReqQueryStringToAMongooseFindFilterStringic(fkey, schemaType, querystr, subType, jrResult)
- Source:
- See:
-
- convertReqQueryStringToAMongooseFindFilter
A string-field-specific version of the function that makes a query object from a query string
Parameters:
| Name | Type | Description |
|---|---|---|
fkey |
* | |
schemaType |
* | |
querystr |
* | |
subType |
* | |
jrResult |
* |
Returns:
query object