[Beginner] complex "where" clause
Hi. I'm getting from frontend app following (quite complex) filtering criteria:
[[["documentDate",">=","2023-11-23T23:00:00.000Z"],"and",["documentDate","<","2023-11-24T23:00:00.000Z"]],"and",[["inOut","contains","FV"],"or",["customerId","contains","FV"],"or",["documentTypeId","contains","FV"],"or",["documentNo","contains","FV"]]]
As it comes from a data grid, a user can specify any criterias basing on available columns
I need to convert these to proper params of where() method. Is it possible with drizzle at all? If so, can someone share some tips how to start? Or maybe the better option is to convert it to plain sql...
TIA19 Replies
do you control the frontend?
this is not a great data interchange format, if so
you could parse this but it would be a lot nicer as a typed nested json structure rather than effectively an array of (anything or array of anything)
Unfortunately it comes from commercial datagrid component, so apparently I have to parse it. But I'm more concerned about drizzle side, all possible "and"s and "or"s mixed together...
Hello, @Marcin.B! I think you can try something like this:
Thanks, @solo , for the tip. How would start with the conversion, not knowing what will come in the criteria? As I wrote, user can specify any criteria using any fields that are in the query.
@Marcin.B wow, it's a good question 😅 I think you should work on converting it to nested json some how
This is doable, is there any spec?
It will require a lot of testing
It looks like all the operations are binary operations
The parsing should be recursive
I can do it, for the right price 🤑
lol
I have made first attempts. Recursion is the only way since arrays can be nested. Conversion to plain sql could be much easier, at least for me 😉 , since we can easily convert schema field names to database names , but I'm really curious how it could be in "drizzle" way.
And @Angelelz what is the right price?
This is the approach I would follow, I asked if there's a spec because we'll need to create a map between all the possible binary operators with their corresponding function to transform it to sql
For example:
I haven't though about the types yet, but left and right could also be another operator of just columns and strings
datagrid comes from DevExpress DevExtreme package - https://js.devexpress.com/Angular/Documentation/Guide/UI_Components/DataGrid/Getting_Started_with_DataGrid/ and this is not an advertisment in any possible way 😉
Angular DataGrid - Getting Started - DevExtreme Angular Documentation
Follow our Angular DataGrid 'how to' guide: Getting Started with DataGrid.
I believe this is outside regular drizzle help. DM me if you'd like any further assistance
@Angelelz Could you at least propose how to deal with "and" and "or" operators in this scenario
Seems like and and or are not binary operators, so you could in theory check the array length and basically assume if it's > 3 it will be an and or an or
You'd need to check the spec to see if that assumption is correct
This doesn't seem to be a beginner task to me, to be honest
I would do this with a TDD approach, just because this seems crucial to get right
I am the beginner (both typescript/drizzle), coming from Pascal/Delphi world, who apparently jumped into very deep water 😨
Lucky for me "and"/"or" operators are not "array types"
this isn't a typescript or drizzle problem, this is a type definition and parsing problem
@Marcin.B this is actually very easy to model, though you should check to make sure there are not
NOT
entriesjk, it's not. have fun with the docs: https://js.devexpress.com/Angular/Documentation/Guide/Data_Binding/Data_Layer/#Reading_Data/Filtering/Binary_Filter_Operations
DevExtreme Angular - Data Layer - DevExtreme Angular Documentation
Follow our Angular Data Layer guide.
Lol, I bet you there's a library out there that can do this parsing for you. This is Js after all
I can probably do it in a day if I don't have to write tests
With test and QC probably a week
@francis Actually the parsing is not the main problem for me. I'm familiar with recurection. My main concern is how to prepare proper where() method, rephrasing it in "plain sql", how to properly set "brackets" for nested and/or operators.
I think I follow @solo example, I have certain ideas....
the recursion is not the hard part, defining a consistent data type in order to think through the recursion is the hard part
you need (or I would recommend) to transform into an intermediate representation that associates an operator with a list of operands first, then you can transform that into the relevant drizzle functions
@francis Thx for the suggestion