[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•12mo 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.BOP•12mo 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•12mo 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.BOP•12mo 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•12mo ago

@Marcin.B wow, it's a good question 😅 I think you should work on converting it to nested json some how

Angelelz•12mo 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.BOP•12mo 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•12mo 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.BOP•12mo 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•12mo ago

I believe this is outside regular drizzle help. DM me if you'd like any further assistance

Marcin.BOP•12mo ago

@Angelelz Could you at least propose how to deal with "and" and "or" operators in this scenario

Angelelz•12mo 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.BOP•12mo 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•12mo 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

francis•12mo ago

jk, 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.

Angelelz•12mo 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.BOP•12mo 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•12mo 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.BOP•12mo ago

@francis Thx for the suggestion

Gaming

Programming

[Beginner] complex "where" clause