How can I combine FilterOperators? And how can I choose AND/OR Filters relation?

[tommasomeli]

I need to combine FiltersOperators, such as $gte and $lte ($between), $not and $in... If I insert the filters as query parameters, only the last one related to a column is considered.

I would also like to understand how to choose the type of relationship between the filters (AND,OR)

[ppetzold]

Seems like a bug then. The intention was to have ?created=$gte:{FROM}&created=$lte:{TO} working.

Looks like it isn't covered by a test either.

Happy to accept a bug fix :)

[evandana]

@tommasomeli Were you able to figure out a solution?

Could you use the PaginateConfig.where option to perform a between? If so, do you have an example of how to do that? I'm running into the same issue and am experimenting with a workaround where I translate the params from the query to the PaginateConfig.where.

[evandana]

Here's a hacky first attempt at a workaround for it:

// controller.ts
@Injectable()
export class ReportsService {
  constructor(
    @InjectRepository(Event) private readonly eventsRepository: Repository<Event>) {}

  async getReportRowsByParams(query: PaginateQuery): Promise<Paginated<Event>> {
    const {
      query: augmentedQuery,
      reportsPaginateConfig: augmentedReportsPaginateConfig 
    } = betweenPolyfill(query, reportsPaginateConfig);

    return paginate(augmentedQuery, this.eventsRepository, augmentedReportsPaginateConfig);
  }
}

// between.polyfill.nestjspaginate.ts
import { filter } from "lodash";
import { FilterOperator, PaginateQuery } from "nestjs-paginate";
import { Between } from "typeorm";

// there's a bug with nestjs-paginate preventing simulating $between filtering using $lte <> $gte
// https://github.com/ppetzold/nestjs-paginate/issues/77
// until that's fixed, this is the best workaround

/**
 * Converts a bounding filter ($lte <> $gte) to a `where` option
 * ONLY for eventDate col (at least for now)
 * @param query 
 */
export function betweenPolyfill(query: PaginateQuery, reportsPaginateConfig) {
  if (Array.isArray(query?.filter?.eventDate) && query?.filter?.eventDate?.length >= 2) {
    const baseOperators = ['gt', 'lt'];
    const eventDateFilters = query.filter.eventDate;
    const dateBounds = {
      lowerOperator: null,
      lowerValue: null,
      upperOperator: null,
      upperValue: null,
    };
    for (let i = eventDateFilters.length - 1; i >= 0 && baseOperators.length; i--) {
      console.log('eventDateFilters', i, baseOperators.length, eventDateFilters, eventDateFilters[i])
      if (baseOperators.length) {
        baseOperators.forEach(baseOperator => {
          const filterMatch = eventDateFilters[i].match(new RegExp(`(${baseOperator}[e]?):(.*)`));
          if (filterMatch) {
            switch (filterMatch[1]) {
              case 'lt':
                dateBounds.upperOperator = filterMatch[1];
                dateBounds.upperValue = filterMatch[2];
                baseOperators.pop();
                break;
              case 'lte':
                dateBounds.upperOperator = filterMatch[1];
                // *add* one millisecond to achieve "or equal" with `Between`
                dateBounds.upperValue = new Date(new Date(filterMatch[2]).getTime() + 1).toISOString();
                baseOperators.pop();
                break;
              case 'gt':
                dateBounds.lowerOperator = filterMatch[1];
                dateBounds.lowerValue = filterMatch[2];
                baseOperators.shift();
                break;
              case 'gte':
                dateBounds.lowerOperator = filterMatch[1];
                // *subtract* one millisecond to achieve "or equal" with `Between`
                dateBounds.lowerValue = new Date(new Date(filterMatch[2]).getTime() - 1).toISOString();
                baseOperators.shift();
                break;
            }
          }
        });
      }
    }
  
    // mutations
    reportsPaginateConfig.where = {
      eventDate: Between(dateBounds.lowerValue, dateBounds.upperValue)
    }
    delete query.filter.eventDate;
    if (Object.keys(filter).length === 0) {
      delete query.filter;
    }
  }

  return {query, reportsPaginateConfig};
}

[Diaskhan]

I guesse is superFeature. recommendation

There must be group filtering similar to this.

Class GroupFilter with property AND or OR property. And GroupFilter must contain GroupFilter inside

Like tree Filter

But hard to say how it will work with typeorm query builder

[FrancYescO]

https://github.com/ppetzold/nestjs-paginate/pull/457

[ppetzold]

Closed by #457