Send data to back-end

[ArmandArthur]

Hello,

Does React Query Builder can send to Data to Back-end?
Or We need to export SQL et copy/paste the WHERE condition?

Thank

[jakeboone02]

React Query Builder is focused on client-side query management (hence "React" in the name) and has some tools for converting queries into various formats irrespective of server- or client-side execution, but has no tools (or really any opinions, for that matter) on the API, network, or database connection layers.

[ArmandArthur]

thank you

[Play-AV]

I feel like I commented here before but apparently not?
Anyways, from a best practice / sanity perspective, you shouldn't be pushing raw SQL (or your chosen DSL) to be executed against your database directly. In an ideal world, you send something 'else' to your backend which does the parsing / validation (so a) is it valid and b) is this 'safe' to run).
I use the query builder state structure, and just parse it (with my own functions) on the backend. It fits nicely with the rest of the elements needed (the sorting, pagination, scoring tweaks etc).
All my operators are named differently and many are focused on full text-y things. Additionally my field names aren't always going to match in the actual target 'tables'.
You really don't want your frontend relying on database implementation / storage details. Reducing that coupling, even with simple things like the field names presented in the query builder, is pretty important.

[jakeboone02]

I saw a notification that a similar message had been posted but then it wasn't here when I looked, so I think you did post it and then something happened. Anyway thanks for the much better answer to @ArmandArthur's question!

[ArmandArthur]

Could you post your parser functions?

[Play-AV]

Could you post your parser functions?

Probably not.

I can try to give you an overview maybe? The functions / code itself going to be very specific to what database / systems you're querying, how much control you have over that, and how much flexibility and complexity you ultimately require.

I guess to start, don't define the 'fields' within your react app or whatever, that doesn't make sense.

Define them somehow in your backend (as I've been fleshing this out along side reworking some of my entity search endpoints, I'm just statically defining them in the backend but that's likely not an ideal option) and have the query builder load those from an endpoint. I define my fields, how they should be presented to the frontend, what the 'fields' actually are in the databases being queried, and then based upon their defined types and some overrides, I can provide the correct data the frontend query builder needs, including the operators as I'm not really using the built in ones.

Now when you build out your queries with query builder, you can send that object representation of the query to your backend (I have like 3 syntaxes I use, a simple 'single level' one, this query builder one, and direct DSL dependent on the database as a debugging option).

In your backend, you'll want a recursive function to walk through the query builder object, generating the correct DSL for your chosen database. If you have some kind of a 'factory' / query builder already this is far easier to reason about.

You just check the combinator / operator types, parse out the value strings (for example, I like utilizing commas for separating terms in simple text inputs, some keyword fields support wildcard stuff). At this point you can also handle the direct 'lookup' / mapping for the front-end provided representation of a field, to however it's handled internally. From that you can actually build the relevant portion of the query.

This provides some benefits, if correctly configured, your query builder's fields are 'driven' by the backend. You can handle whatever implementation complexity, or field name to database field name abstraction, without really mucking about in your frontend. It's cleaner, and if exposing those internal implementation details is a problem, this has you covered.

You don't need to worry about parsing the query builder state on the frontend, just shove it at your backend, and maybe validate it.

You aren't executing raw SQL or DSL provided by a 'user' against your chosen database. Don't do this anyways. Even in internal systems where you're not super concerned about malicious actors or anything like that, people write stupid queries without noticing, especially in a visual query builder. Considering that limiting, 'pagination' and other things are really outside of the scope here, it's not far fetched to assume some really like 'sorry I took down the database' queries could be generated and executed.

[jakeboone02]

I guess to start, don't define the 'fields' within your react app or whatever, that doesn't make sense.

Define them somehow in your backend (as I've been fleshing this out along side reworking some of my entity search endpoints, I'm just statically defining them in the backend but that's likely not an ideal option) and have the query builder load those from an endpoint. I define my fields, how they should be presented to the frontend, what the 'fields' actually are in the databases being queried, and then based upon their defined types and some overrides, I can provide the correct data the frontend query builder needs, including the operators as I'm not really using the built in ones.

Completely agree, and that's what we do at my day job. We have a configuration table that maps the internal column names to the field names the UI actually uses, plus other info like data type/size (which implies the operator list), valid value lists, etc. That config gets converted to JSON and sent to the client as a Field[]. (We stick it in the original HTML response to simplify initialization of our grid component, but it could be async.)

Now when you build out your queries with query builder, you can send that object representation of the query to your backend (I have like 3 syntaxes I use, a simple 'single level' one, this query builder one, and direct DSL dependent on the database as a debugging option).

Same. Our API ended up weird (because the frontend team had very little input), so we have to do some transformation on the client side before sending the query object to the server, but we don't assume anything sent from the client to be "safe."

In your backend, you'll want a recursive function to walk through the query builder object, generating the correct DSL for your chosen database. If you have some kind of a 'factory' / query builder already this is far easier to reason about.

I'm curious if the transformQuery function helps you at all here? Or is your backend not even a JS runtime? Seems like it might be an opportunity to enhance the library if you have some reusable, generic logic in there.

The rest of your response is very good advice! We should probably put up an "Architectural Considerations" page in the "Tips" section of website since these kinds of questions come up fairly regularly.

[Play-AV]

It's great to know that I'm mostly on the right track here with how I've approached integrating this.

As for transformQuery (and formatQuery). I considered it and did do some quick and dirty experiments.

I might as well give some long winded background, this backend service (the ‘Entity Search' service in particular) is indeed TS/JS. However, discovering that RQB existed and satisfied my needs from a frontend perspective, coincided with me getting underway on a major rework of this Entity Search service itself (why do you think I was looking around for a competent query builder?)

It's funny, I had drawn out this data model for relatively generic, database system agnostic, nested multi combinator queries (that's really just a terrible mess of words), and it looked very close to what you’ve done with RQB (seriously, your data model is pretty on point). Maybe it’s been a nitpick of mine but I’ve checked out a lot of patterns for communicating / defining more complex queries, and to me, they often trend too close to a particular DSL, or the structure / schema doesn’t really support the expressiveness of possible queries.

I'm using mostly my own operators and the fields themselves have relatively complicated definitions. These not only lay out the "actual" columns / fields to target but in some cases, the definition itself, or parameters provided as part of query options, may specify rules or additional parameters that are to be used in the construction of DSL.

For those still reading, here’s an example rule from RQB, real simple stuff. The operator isn't standard but you can easily figure out what's going on.

{

         "id": "67e06e724f55ea8026bb60df",

         "field": "artist",

         "operator": "FTPhrase",

         "value": "Polyphonic Spre"

}

Internally an artist name column, field, whatever you want to call it, isn't “artist”, it's an "artistName", and wouldn't it maybe make sense to toss "track.artistName" in there too and query against that field at the same time? Alright, that's easy enough.

Now, there's probably a pretty good chance I'm more interested in releases where they're the main artist. Somewhat dependent on the target database system , maybe I want to massage the scoring or weights a bit, either on this specific 'rule' or the broader query.
These aren’t pure ‘filtering’ operators though, so conceptually and result expectations wise, I’m fine with that idea.

In this contrived example, I'm leaving out the whole 'override scoring or option flags targeting specific rule Ids to take precedence over the built in field defaults' stuff. It's mostly irrelevant, beyond the scope here, and likely relatively easy for someone to sort out if needed.

Anyways, maybe you can start to see why I figured just biting the bullet and going at this from a mostly blank slate was appealing, versus trying to make transform/format work here?

I’m not particularly sure how much of what I’ve got going on in the backend, is hugely relevant to the actual frontend component but give me some time to chew it over and I may have some ideas in regards to that.

I think an Architecture Considerations page would be extremely valuable.

I'm sure there are other people using the QueryBuilder for complex, dynamic domains maybe unsure of some of these patterns or haven’t even considered them as options. For example, simply providing fields via your backend can open up doors to selectively in the fields available, you could base this on user permissions (keeping internal fields that are rarely needed or a risk to be queried, safely hidden from most users) or maybe you just want to define complexity ‘levels’ for fields that can be toggled or changed by the user as needed.

[jakeboone02]

It's funny, I had drawn out this data model for relatively generic, database system agnostic, nested multi combinator queries (that's really just a terrible mess of words), and it looked very close to what you’ve done with RQB (seriously, your data model is pretty on point). Maybe it’s been a nitpick of mine but I’ve checked out a lot of patterns for communicating / defining more complex queries, and to me, they often trend too close to a particular DSL, or the structure / schema doesn’t really support the expressiveness of possible queries.

I wish I could take credit for the elegant query structure, but the basics of that and the React component model were already in place when I took over as maintainer. You won't see the original author much in git blame, but that's mostly due to several major refactorings like the TypeScript conversion and migration to a monorepo. I've really only built on top of a great foundation.

I'm using mostly my own operators and the fields themselves have relatively complicated definitions. These not only lay out the "actual" columns / fields to target but in some cases, the definition itself, or parameters provided as part of query options, may specify rules or additional parameters that are to be used in the construction of DSL.

This can definitely get complicated and very specific to each implementation, but I'm wondering if it would be worth developing a sample utility that could take a table or view specification and convert it to a Field[] based on datatypes and other metadata (foreign keys...?). This may end up being a single SQL query you could run against your database. We could develop one for each of the major database platforms. If nothing else it could be a good starting point.

Anyways, maybe you can start to see why I figured just biting the bullet and going at this from a mostly blank slate was appealing, versus trying to make transform/format work here?

Yep, sometimes it's better to just start from scratch. The meat of transformQuery itself is something like 30 LOC, so you're not missing out on much by rolling your own.

I’m not particularly sure how much of what I’ve got going on in the backend, is hugely relevant to the actual frontend component but give me some time to chew it over and I may have some ideas in regards to that.

Looking forward to your input!

I think an Architecture Considerations page would be extremely valuable.

Ditto. I'll start working on it soon.