Net9 Abp Vnext查询、高级搜索、过滤终极解决方案，ORM支持Freesql/SqlSugar/EFCore或原生sql

先上效果图

以员工管理表为例，常用栏位如下图

基本需求：默认搜索框可以模糊查询搜索工号、姓名、手机号、年龄等不需要关联查询基本字段。

特殊需求需要高级搜索：例如按入职区间、部门、公司、年龄段、上级主管等进行模糊搜索，且支持并且或者等关系(and/or)，实体表需要关联查询

当用户有其它需求，系统表无法满足时，用户需要自定义字段，Abp使用的是扩展字段（json存储），允许使用扩展字段精准搜索

市场主流的数据库都要支持，例如mysql/sqlserver/oracel/pgsql/sqlite

如下图用户添加了自定义字段Input

实体表

自定义字段精准搜索原理，调用原生sql的json数据查询，目前市场主流和的几大数据库支持（mysql/sqlserver/oracel/pgsql/sqlite)

语法参考下图

多个搜索值用空格分开进行搜索

经常用的搜索条件通过下图保存

特殊情况用右侧的高级搜索

搜索条件非常多时，可以从excel中复制，如下图

高级搜索配置

配置后的搜索界面

高级搜索实体表

新建表TSYS_Filters与TSYS_FiltersDetail记录搜索条件

自定义字段json数据查询

 前端调用通过字段类型filedType=2判断是否有自定义字段搜索

完整的前端代码

前端组件定义

AdvancedSearch.vue代码

<template>
  <t-select-input :value="selectValue" :popup-visible="popupVisible"
    :popup-props="{ overlayInnerStyle: { padding: '6px' } }" :placeholder="$t('pages.common.advancedSearchPlaceholder')"
    allow-input style="width: 220px" autocomplete="" @input-change="onInputChange"
    @popup-visible-change="onPopupVisibleChange" @enter="onEnter">
    <template #panel>
      <ul>
        <li class="t-select-option" v-for="item in options" :key="item" @click="() => onOptionClick(item)">
          {
  { item.label }}
        </li>
      </ul>
    </template>
    <template #prefix-icon>
      </SearchIcon> -->
      <t-button shape="square" variant="text" style="margin: 0 -10px" :loading="loading" @click="searchClick">
        <template #icon><search-icon /></template>
      </t-button>
    </template>
    <template #suffixIcon>
      <chevron-down-icon v-if="!popupVisible" @click="iconClick" />
      <chevron-up-icon style="color: #0052d9" v-if="popupVisible" />
      <t-button variant="text" shape="circle" style="margin: 0 -10px 0 0" :title="$t('pages.common.advancedSearch')"
        theme="default" @click="() => onAdvancedQueryClick()">
        <template #icon>
          <ellipsis-icon />
        </template>
      </t-button>
    </template>
  </t-select-input>
  <search-dialog ref="advancedSearchRef" :filters="filters" :fileds="fileds" :enumData="enumData"
    :filterCode="$props.filterCode" :options="options" @dynamicFilter="dynamicFilter" @refreshOptions="refreshOptions"
    @loadingStart="loadingStart" />
</template>
<script setup lang="ts">
import { t } from '@/locales';
import SearchDialog from './SearchDialog.vue';
import { ref, onMounted, watch } from 'vue';
import { SelectInputProps, SelectInputValue, SelectInputFocusContext } from 'tdesign-vue-next';
import { ChevronDownIcon, ChevronUpIcon, SearchIcon, EllipsisIcon } from 'tdesign-icons-vue-next';
import { globalPageSize, globalDateFormat, globalDateTimeFormat, globalTimeFormat, } from '@/config/global';
import { getEnumCode } from '@/api/system/enum';
import { filterFilters, getFilters, filterItemFilters, getFiltersCode } from '@/api/system/filters';
import dayjs from 'dayjs';
import duration from 'dayjs/plugin/duration';//参考文档https://day.js.org/docs/zh-CN/durations/durations
import { cloneDeep, forIn } from 'lodash';
const props = defineProps({
  visible: {
    type: Boolean,
    default: false,
  },
  filters: {
    type: Object,
    default: () => { },
  },
  filterCode: {
    type: String,
  },
  fileds: {
    type: Array,
    default: () => [],
  },
});
const loading = ref(false);
const filters = ref([]);
const fileds = ref([]);
const enumData = ref({});
const inputFileds = ref([]);
const advancedSearchRef = ref();
const emits = defineEmits(['dynamicFilter']);
const dynamicFilter = (filtersValue) => {
  let filterInfo = cloneDeep(filtersValue);
  filterInfo.Filters = filterInfo.Filters.map((item) => {
    if (item.filedType === 2) {
      item.Value = JSON.stringify({ Field: item.Field, Operator: item.Operator, Value: item.Value });
      item.Operator = 'Custom';
      item.Field = 'QueryJson 命名空间.DynamicFilterCustomImpl,程序集名称';
    }
    return { Field: item.Field, Operator: item.Operator, Value: item.Value };
  });
  let filtersPage = {
    FilterInfo: filterInfo,
    PageSize: globalPageSize,
  };
  emits('dynamicFilter', filtersPage);
};
// 搜索处理逻辑
const popupVisible = ref(false);
const selectValue = ref();
const options = ref([]);//搜索方案
const onOptionClick = (item) => {
  popupVisible.value = false;
  let filterInfo = JSON.parse(item.value);
  filterInfo.Filters = filterInfo.Filters.map((item) => {
    if (item.filedType === 2) {
      item.Value = JSON.stringify({ Field: item.Field, Operator: item.Operator, Value: item.Value });
      item.Operator = 'Custom';
      item.Field = 'QueryJson 命名空间.DynamicFilterCustomImpl,程序集名称';
    }
    return { Field: item.Field, Operator: item.Operator, Value: item.Value };
  });
  let filtersPage = {
    FilterInfo: filterInfo,
    PageSize: globalPageSize,
  };
  emits('dynamicFilter', filtersPage);
};
const iconClick = () => {
  popupVisible.value = !popupVisible.value;
};
const searchClick = () => {
  onEnter(selectValue.value);
};
const onInputChange = (keyword) => {
  popupVisible.value = false;
  selectValue.value = keyword;
};
const onEnter = (value) => {
  loading.value = true;
  let operator = 'Contains';
  if (value) {
    value = value.replaceAll(' ', ',').replaceAll('　', ',').replaceAll('，', ',').replaceAll('；', ',').replaceAll(';', ',');
  }
  if (value.indexOf(',') > 0) {
    operator = 'Any';
  }
  let filtersValue = inputFi