How to create dynamic filtering in db?

My end-users can create “Activity Types” with their own custom fields, so they can create records like:

          "id": 324714,
          "by_user": 1,
          "date_time": "2022-10-14T08:39:59.000Z",
          "company": 1,
          "custom_fields": {
            "call_id": "1355357986640381620",
            "call_end": "2022-10-14T08:40:36Z",
            "call_start": "2022-10-14T08:39:59Z",
            "call_duration": 37

I figured out how to create custom queries to filter results by the json values, but how do I create a query/flow where the query is done dynamically trough conditions from json?

So for example when the user creates:

      "conditions": {
        "teams": [],
        "fields": [
            "field": "call_duration",
            "value": 100,
            "operator": "bigger_than"

The field will be fully dynamic as the end-user will create both the activity fields, and the conditions.

Anyone recommendations on what I have to learn / use to create SQL queries from server connect to create these dynamic filters? Do I have to use “knex” to dynamically create theses type of queries?

Edit: The suggestion in howto/dynamic-sql-query/45192 seems to work, I guess I can put the conditions in a repeat and add the raw sql query in string format. Let me know please if this isn’t a good idea

Community Page
Last updated: