DT
Drizzle Team•11mo ago
Marcin.B

[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... TIA
19 Replies
francis
francis•11mo ago
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)
Marcin.B
Marcin.BOP•11mo ago
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...
Mykhailo
Mykhailo•11mo ago
Hello, @Marcin.B! I think you can try something like this:
const response = await db
.select()
.from(documents)
.where(
and(
gte(documents.documentDate, '2023-11-23T23:00:00.000Z'),
lt(documents.documentDate, '2023-11-24T23:00:00.000Z'),
or(
ilike(documents.inOut, '%FV%'),
ilike(documents.customerId, '%FV%'),
ilike(documents.documentTypeId, '%FV%'),
ilike(documents.documentNo, '%FV%'),
),
),
);
const response = await db
.select()
.from(documents)
.where(
and(
gte(documents.documentDate, '2023-11-23T23:00:00.000Z'),
lt(documents.documentDate, '2023-11-24T23:00:00.000Z'),
or(
ilike(documents.inOut, '%FV%'),
ilike(documents.customerId, '%FV%'),
ilike(documents.documentTypeId, '%FV%'),
ilike(documents.documentNo, '%FV%'),
),
),
);
Marcin.B
Marcin.BOP•11mo ago
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.
Mykhailo
Mykhailo•11mo ago
@Marcin.B wow, it's a good question 😅 I think you should work on converting it to nested json some how
Angelelz
Angelelz•11mo ago
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
Marcin.B
Marcin.BOP•11mo ago
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?
Angelelz
Angelelz•11mo ago
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:
const operatorMap = {
">=": (left, right) => gte(left, right)
...
}
const operatorMap = {
">=": (left, right) => gte(left, right)
...
}
I haven't though about the types yet, but left and right could also be another operator of just columns and strings
Marcin.B
Marcin.BOP•11mo ago
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.
Angelelz
Angelelz•11mo ago
I believe this is outside regular drizzle help. DM me if you'd like any further assistance
Marcin.B
Marcin.BOP•11mo ago
@Angelelz Could you at least propose how to deal with "and" and "or" operators in this scenario
Angelelz
Angelelz•11mo ago
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
Marcin.B
Marcin.BOP•11mo ago
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"
francis
francis•11mo ago
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 entries
Angelelz
Angelelz•11mo ago
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
Marcin.B
Marcin.BOP•11mo ago
@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....
francis
francis•11mo ago
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
Marcin.B
Marcin.BOP•11mo ago
@francis Thx for the suggestion
Want results from more Discord servers?
Add your server