relative dates mongodb or anything

[Pyakz]

Description of the feature

relative date search
is this possible?

Use case

[jakeboone02]

At my day job we manage this with a specially-formatted string value, something like "@today - 2 days". This string gets parsed and rendered as separate controls within a custom value editor. In hindsight, we probably should have used an object instead of a string, so in the example below that's what I've done.

It's not much more than a custom value editor that falls back to the default editor, plus a custom rule processor for formatQuery (that also falls back to its respective default). The rule processor doesn't produce valid MongoDB syntax but hopefully it points you in the right direction.

Working sandbox: https://codesandbox.io/p/devbox/cwgk9v?file=%2Fsrc%2FApp.tsx

import { useState } from 'react';
import type { FieldByValue, RuleGroupType, RuleProcessor, ValueEditorProps, ValueOption} from 'react-querybuilder';
import { QueryBuilder, ValueEditor, defaultRuleProcessorMongoDB, formatQuery, useValueEditor } from 'react-querybuilder';

const fields: FieldByValue[] = [
  { value: 'firstName', label: 'First Name' },
  { value: 'lastName', label: 'Last Name' },
  {
    value: 'servicedate',
    label: 'Service Date',
    operators: [
      // You can use custom operators (the value doesn't have to be ">="),
      // you just have to handle them separately in `ruleProcessor`.
      { value: '>=', label: 'is during the previous' },
    ],
    // Setting a default value helps avoid invalid values.
    defaultValue: { value: 2, unit: 'days' },
  },
];

const initialQuery: RuleGroupType = {
  combinator: 'and',
  rules: [
    { field: 'servicedate', operator: '>=', value: { value: 2, unit: 'days' } },
  ],
};

const durationUnits = [
  { value: 'days', label: 'day(s)' },
  { value: 'weeks', label: 'week(s)' },
  { value: 'years', label: 'year(s)' },
  { value: 'rolling days', label: 'rolling day(s)' },
  { value: 'rolling weeks', label: 'rolling week(s)' },
  { value: 'rolling years', label: 'rolling year(s)' },
] satisfies ValueOption[];

const MyValueEditor = (props: ValueEditorProps) => {
  const ve = useValueEditor(props);

  if (props.field !== 'servicedate') {
    // If you call `useValueEditor` in your custom value editor, always
    // pass `skipHook` to the fallback/default value editor.
    return <ValueEditor {...props} skipHook />;
  }

  return (
    <div className={props.className}>
      <input
        type="number"
        // This classname is optional, it just enables some default styles like spacing.
        className={ve.valueListItemClassName}
        value={props.value?.value ?? ''}
        onChange={e =>
          props.handleOnChange({
            ...props.value,
            value: e.target.valueAsNumber,
          })
        }
      />
      <select
        className={ve.valueListItemClassName}
        value={props.value?.unit ?? 'days'}
        onChange={e =>
          props.handleOnChange({ ...props.value, unit: e.target.value })
        }>
        {durationUnits.map(du => (
          <option key={du.value} value={du.value}>
            {du.label}
          </option>
        ))}
      </select>
    </div>
  );
};

const ruleProcessor: RuleProcessor = (rule, opts) => {
  if (rule.field === 'servicedate') {
    // TODO: Replace this with proper MongoDB syntax
    return `{ "$where": "servicedate is after (today - ${rule.value.value} ${rule.value.unit})" }`;
  }
  return defaultRuleProcessorMongoDB(rule, opts);
};

export const App = () => {
  const [query, setQuery] = useState(initialQuery);

  return (
    <div>
      <QueryBuilder
        fields={fields}
        query={query}
        onQueryChange={setQuery}
        controlElements={{ valueEditor: MyValueEditor }}
      />

      <h4>Query</h4>
      <pre>
        <code>
          {JSON.stringify(
            JSON.parse(
              formatQuery(query, { format: 'mongodb', ruleProcessor })
            ),
            null,
            2
          )}
        </code>
      </pre>
    </div>
  );
};

[Pyakz]

yoow lets say i have saving these filters as JSON object in mongodb.

i think in your example, its only a one time use for the query.

our app has these custom query like "Paid Reports Last Week"

this will automatically do its job regardless what week it its currently

[Pyakz]