Complex Query Construction

[suvan92]

Hi there,

I'm trying to construct to use the query builder to construct something like the following:

SELECT * FROM resource INNER JOIN resource_owner ON (resource.owner_id = resource_owner.id) 
WHERE resource.created_at BETWEEN $1 AND ($1 + INTERVAL '90 days') AND resource_owner.user_id = $2)

I've been playing around with the query builder for a few days now and I think the config should look something like this:

const config = {
      ...InitialConfig,
      fields: {
        opportunity: {
          type: '!struct',
          label: 'Opportunity',
          subfields: {
            created_at: {
              type: 'datetime',
              label: 'Created (Self Sourced)',
              operators: ['between'],
              valueSources: ['func'],
              funcs: ['addDaysToField'], // Custom function
            },
          },
        },
      },

      funcs: {
        addDaysToField: {
          label: 'Add Days to Date',
          returnType: 'time',
          args: {
            field: {
              label: 'Date Field',
              type: 'field',
              fieldTypes: ['datetime'],
            },
            days: {
              label: 'Days',
              type: 'number',
            },
          },
          formatFunc: (field, args) => `DATE_ADD(${field}, INTERVAL ${args.days} DAY)`,
          sqlFunc: 'DATE_ADD', // SQL function for execution
        },
      },
    }

But the 2 fields that show up for the between selector are static date fields when instead they should be 1) a drop down to select which property of the user we should be adding the days to and 2) a number field to select the number of days to add to the given variable.

Any help would be greatly appreciated! Thank you!