[
https://issues.apache.org/jira/browse/FINERACT-1910?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17750227#comment-17750227
]
Peter Santa edited comment on FINERACT-1910 at 8/2/23 10:23 AM:
----------------------------------------------------------------
Márta Jankovics
yesterday
*GET {{{}url{}}}/datatables/<datatable name>/query?*
{{{}url{}}}/datatables/dt_savings_transaction_1/query?<query parameters>
Query parameters:
* columnFilter: datatable column name (camel case supported), mandatory
* valueFilter: datatable column value
select where columnFilter = valueFilter
* resultColumns: “,“ concatenated list of datatable column names (camel case
supported)
default: primary key
Example:
{{{}url{}}}/datatables/dt_savings_transaction_1/query?columnFilter=bankTransactionCode&valueFilter=1_123&resultColumns=partner_account_id,createdAt
Response:
Non-pageable list of requested result columns
The result is not sorted
*POST {{{}url{}}}/datatables/<datatable name>/query*
Example uri:
{{{}url{}}}/datatables/dt_savings_transaction_1/query
Request body: PagedRequest<AdvancedQueryData>
dateFormat, locale are not supported, but tenant locale and ISO patterns
{{{}yyyy-MM-dd{}}}, {{yyyy-MM-dd HH:mm:ss}} are used
Camel case is supported for column names.
{{columnFilters}} is optional, but {{column}} is mandatory for a filter,
{{values}} can be null depending on the operator ({_}NULL{_}, _NNULL_ do not
accept values, {_}BTW{_}, _NBTW_ require two parameters, IN, _NIN_ accepts
minimum 1 parameter(s), other operators require 1 parameter)
{{{}resultColumns{}}}, {{{}page{}}}, {{{}size{}}}, {{sorts}} are optional but
sort {{property}} is mandatory
operators:
{_}EQ{_}("="),
{_}NEQ{_}("<>"),
{_}GTE{_}(">="),
{_}LTE{_}("<="),
{_}GT{_}(">"),
{_}LT{_}("<"),
{_}LIKE{_}("LIKE")
{_}NLIKE{_}("NOT LIKE")
{_}BTW{_}("BETWEEN")
{_}NBTW{_}("NOT BETWEEN")
{_}IN{_}("IN")
{_}NIN{_}("NOT IN")
{_}NULL{_}("IS NULL")
{_}NNULL{_}("IS NOT NULL")
Example request:
{code:json}
{
"request" :
{
"columnFilters": [
{
"column": "partnerName",
"filters": [
{
"operator": "LIKE",
"values": ["art"]
}
]
},
{
"column": "bankTransactionCode",
"filters": [
{
"operator": "LIKE",
"values": ["123"]
}
]
},
{
"column": "savingsTransactionId",
"filters": [
{
"operator": "GTE",
"values": ["1"]
}
]
},
{
"column": "createdAt",
"filters": [
{
"operator": "BTW",
"values": ["2023-07-26 00:00:00", "2023-07-27 00:00:00"]
},
{
"operator": "LT",
"values": ["2023-07-27 00:00:00"]
}
]
}
],
"resultColumns": ["savingsTransactionId", "partnerAccountId",
"createdAt", "bankTransactionCode"]
},
"page": 0,
"size": 100,
"sorts": [
{
"property": "createdAt",
"direction": "ASC"
}
]
}
{code}
Response:
Pageable list of requested result columns, defaults to the primary key column.
The result is sorted by the {{sorts}} columns, defaults to the primary key
column.
was (Author: peter.santa):
Márta Jankovics
yesterday
*GET {{{}url{}}}/datatables/<datatable name>/query?*
{{{}url{}}}/datatables/dt_savings_transaction_1/query?<query parameters>
Query parameters:
* columnFilter: datatable column name (camel case supported), mandatory
* valueFilter: datatable column value
select where columnFilter = valueFilter
* resultColumns: “,“ concatenated list of datatable column names (camel case
supported)
default: primary key
Example:
{{{}url{}}}/datatables/dt_savings_transaction_1/query?columnFilter=bankTransactionCode&valueFilter=1_123&resultColumns=partner_account_id,createdAt
Response:
Non-pageable list of requested result columns
The result is not sorted
*POST {{{}url{}}}/datatables/<datatable name>/query*
Example uri:
{{{}url{}}}/datatables/dt_savings_transaction_1/query
Request body: PagedRequest<AdvancedQueryData>
dateFormat, locale are not supported, but tenant locale and ISO patterns
{{{}yyyy-MM-dd{}}}, {{yyyy-MM-dd HH:mm:ss}} are used
Camel case is supported for column names.
{{columnFilters}} is optional, but {{column}} is mandatory for a filter,
{{values}} can be null depending on the operator ({_}NULL{_}, _NNULL_ do not
accept values, {_}BTW{_}, _NBTW_ require two parameters, IN, _NIN_ accepts
minimum 1 parameter(s), other operators require 1 parameter)
{{{}resultColumns{}}}, {{{}page{}}}, {{{}size{}}}, {{sorts}} are optional but
sort {{property}} is mandatory
operators:
{_}EQ{_}("="),
{_}NEQ{_}("<>"),
{_}GTE{_}(">="),
{_}LTE{_}("<="),
{_}GT{_}(">"),
{_}LT{_}("<"),
{_}LIKE{_}("LIKE")
{_}NLIKE{_}("NOT LIKE")
{_}BTW{_}("BETWEEN")
{_}NBTW{_}("NOT BETWEEN")
{_}IN{_}("IN")
{_}NIN{_}("NOT IN")
{_}NULL{_}("IS NULL")
{_}NNULL{_}("IS NOT NULL")
Example request:
{{}}
{code:java}
{ "request" : { "columnFilters": [ { "column": "partnerName", "filters": [ {
"operator": "LIKE", "values": ["art"] } ] }, { "column": "bankTransactionCode",
"filters": [ { "operator": "LIKE", "values": ["123"] } ] }, { "column":
"savingsTransactionId", "filters": [ { "operator": "GTE", "values": ["1"] } ]
}, { "column": "createdAt", "filters": [ { "operator": "BTW", "values":
["2023-07-26 00:00:00", "2023-07-27 00:00:00"] }, { "operator": "LT", "values":
["2023-07-27 00:00:00"] } ] } ], "resultColumns": ["savingsTransactionId",
"partnerAccountId", "createdAt", "bankTransactionCode"] }, "page": 0, "size":
100, "sorts": [ { "property": "createdAt", "direction": "ASC" } ] }{code}
{{}}
Response:
Pageable list of requested result columns, defaults to the primary key column.
The result is sorted by the {{sorts}} columns, defaults to the primary key
column.
> Data Table query - Advanced
> ---------------------------
>
> Key: FINERACT-1910
> URL: https://issues.apache.org/jira/browse/FINERACT-1910
> Project: Apache Fineract
> Issue Type: New Feature
> Components: Data Tables
> Reporter: Peter Santa
> Priority: Critical
> Labels: BeanSalad
> Attachments: dataTableAdvancedFilteringExample.txt,
> image-2023-08-02-12-21-56-564.png, image-2023-08-02-12-22-23-468.png,
> image-2023-08-02-12-22-36-686.png
>
>
> h1. Background
> FINERACT-1747
> h1. Goal
> Have the querying possibilities - that have been developed with FINERACT-1747
> - extended with the following features:
> * pagination - similarly to several Fineract API endpoints
> * sorting based on given attribute
> * filter for closed (greather-than-or-equal, less-than-or-equal) interval in
> case of the following typed fields:
> ** date
> ** date and time
> ** number
> ** decimal
> ** string/text that can be parsed to numeric
> * contains search in string and text fields
> * exact match for string and text fields
> The filtering parameters should be applied with "AND" relation.
> h1. Solution Concept
> Have the solution concept aligned between
> * FINERACT-1910
> * FINERACT-1912
> * FINERACT-1915
> If multiple columns would be used as column filter, it is advisable not to
> put this number of query parameters in the url, but in the body, like
> graphql. With this, a request would look like:
> h3. URL
> GET
> {{{}url{}}}/datatables/{{{}dataTableId{}}}/query?resultColumns=column3,column6,column4&offset=0&limit=10&sortOrder=desc&orderBy=transaction_date
> h3. Body
> See the attached [^dataTableAdvancedFilteringExample.txt].
> The filtering parameters should be applied with "AND" relation.
>
> <Solution Concept to be extended by developers>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
