一、问提描述
使用elementUI table 官方筛选案例，发现筛选不是服务器端筛选，而是浏览器端对每一页进行单独筛选。 如何在服务器端筛选？
二、查询Element UI 官网table组件发现：
1、Afilter-change事件，说明：当表格的筛选条件发生变化的时候会触发该事件，参数的值是一个对象，对象的 key 是 column 的 columnKey，对应的 value 为用户选择的筛选条件的数组。参数：filters。
2、 prop属性，说明：对应列内容的字段名，也可以使用 property 属性。类型 string
3、filters属性，说明：数据过滤的选项，数组格式，数组中的元素需要有 text 和 value 属性。类型 Array[{test,value}]
4、column-key属性，说明：column 的 key，如果需要使用 filter-change 事件，则需要此属性标识是哪个 column 的筛选条件 类型：string
三、用法描述
1.在 el-table 标签 @filter-change="handleFilterChange" ，
2. 在vue周期函数methods: {}中实现
handleFilterChange 方法：可以监听整个table中过滤条件变化； --这个事件非常重要，这里它还充当了与服务器进行数据交互的入口。这是过滤方法不能提供的，因为过滤方法逐行执行，执行次数太多。
setFilter方法：按照服务器api需要的格式组装过滤条件；此处需要在data（{returen{ }}）中定义一个中间变量this.filter:[] ，用来保存和更新过滤条件。
getList（）方法：发送请求；
3 在 el-table-column 列中，当以过滤条件 :filters="userStatusNameFilters"、column-key="UserStatusName"、prop="UserStatusName" 三者缺一不可，且column-key的值必须与prop一致，也就是必须为字段名称"。若不定义column-key的值，那么handleFilterChange （filter）返回值filter对象的名称将会是一个自动生成的值。
4 在data（）{return{ userStatusNameFilters: [] }} 定义数组 。如果数据是固定不变的可以在js中直接写入值 serStatusNameFilters: [{text：‘管理员’，value：‘管理员’}，{text：‘普通用户’，value：‘普通用户’}] 。如果数据可能有变化，需要从服务器端取值。
四、代码描述：
前端代码：
<el-table v-loading="listLoading" :key="tableKey" :data="list" :border="false" :stripe="true" size="small" style=" 100%;" @filter-change="handleFilterChange" > <el-table-column :filters="regionNameFilters" column-key="RegionName" label="行政区域" prop="RegionName" align="center" width="120px" />
methods: {
// 当talbel中任何一列的过滤条件点击确定和覆盖时，都会触发此事件。
handleFilterChange(filters) {
// console.log(filters)
// console.log('筛选条件发生变化')
let row = null
let val = null
// 拷贝filters的值。
for (const i in filters) {
row = i // 保存 column-key的值，如果事先没有为column-key赋值，系统会自动生成一个唯一且恒定的名称
val = filters[i]
}
const filter = [{
row: row,
op: 'contains',
value: val
}]
// console.log(filter)
this.setFilter(filter)
},
getList() { this.listLoading = true var filters = [] for (var i in this.filters) { // 去除value数组为空的值 if (this.filters[i].value && this.filters[i].value.length > 0) { filters.push({ 'field': this.filters[i].row, 'op': this.filters[i].op, 'value': this.filters[i].value }) } } if (filters.length > 0) { // 将 JavaScript 值(通常为对象或数组)转换为 JSON 字符串 this.listQuery.filters = JSON.stringify(filters) } else { this.listQuery.filters = null } this.listQuery.query = this.queryInfo console.log(filters) getList(this.listQuery).then(response => { // console.log(response.data.rows); this.list = response.data.rows this.total = response.data.total this.listLoading = false }) }, // 通过中间变量this.filters数组，保存当前table中所有列过滤条件的变化。 setFilter(filters) { for (var i in filters) { var filter = null for (var j in this.filters) { // 如果filters[i]中存在于this.filter[]相同的值，那么把当前this.filter[i]的引用覆盖filter的引用. if (filters[i]['row'] === this.filters[j]['row']) { filter = this.filters[j] } } // 如果filter为空，即不存在相同的值，则将当前filter[i]添加到this.filter[] if (filter == null) { this.filters.push({ 'row': filters[i].row, 'op': filters[i].op, 'value': filters[i].value }) } else { // 如果filter不为空，即存在相同的值。则将filter[i] 赋值给filter，本质是更新this.filter[i]的值。 filter.value = filters[i].value filter.op = filters[i].op } } // console.log(this.filters) this.listQuery.page = 1 this.getList() }, getRegionName() { getRegionName().then(response => { var temp = [] for (var i = 0; i < response.data.length; i++) { temp.push({ text: response.data[i].RegionName, value: response.data[i].RegionName }) } this.regionNameFilters = temp.slice(0) // console.log(this.regionNameFilters) }) }, } //getList、getRegionName 是对axios异步请求的封装。对应后端的一个api，。
table结合分页显示：
//element组件 <el-pagination :current-page="listQuery.page" :page-sizes="[10,15,20,30, 50]" :disabled="enumLoading" :page-size="listQuery.limit" :total="total" background layout="total, sizes, prev, pager, next, jumper" @size-change="handleSizeChange" @current-change="handleCurrentChange" /> </div> //变量
total: null, //共多少行数据
enumLoading: false, listQuery: { page: 1, //默认从第一页开始 limit: 15, //默认每页15行 filters: [] //存储需要过滤字段名称和值 }, //方法 handleSizeChange(val) { this.listQuery.limit = val this.getList() }, handleCurrentChange(val) { this.listQuery.page = val this.getList() },
后端代码（C#、linq实现）：
[HttpGet]
[Route("List")]
public ApiResult GetList(int page, int limit, string sort = null, string order = null, string filters = null, string query = null)
{
PageModel p = new PageModel();
if (filters != null)
{
p.filters = Newtonsoft.Json.JsonConvert.DeserializeObject<Filter[]>(filters);
}
p.page = page;
p.rows = limit;
p.sort = sort;
p.order = order;
if (p.page <= 0)
{
p.page = 1;
}
if (p.rows <= 0)
{
p.rows = 10;
}
var data = manage.GetQueryable().Select(d => d);
//过滤
data = data.Where(p.filters);
//搜索条件
if (query != null && query.Length > 0)
{
data = data.Where(new string[] { "UserName", "RealName" }, query);
}
//排序
if (order != "normal" && sort != null)
{
bool isAsc = order == "asc";
data = data.OrderBy(new[] { sort }, new[] { isAsc });
}
else
{
//默认排序
data = data.OrderBy(d => d.UserID);
}
DataModel pageData = new DataModel();
pageData.page = p.page;
pageData.total = data.Count();
pageData.rows = data.Skip((p.page - 1) * p.rows).Take(p.rows).ToList();
ApiResult result = new ApiResult();
result.success = true;
result.data = pageData;
return result;
}
[HttpGet] [Route("RegionName")] public ApiResult GetRegionName() { ApiResult result = new ApiResult(); result.success = true; result.data = manage.GetRegionData().Select(d => new { id = d.RegionID, name = d.RegionName }).ToList(); return result; }
where orderby 重载
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Web;
namespace PastureSpace.Models
{
public static class QueryableExtension
{
public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, string[] propertyName, bool[] ascending) where T : class
{
Type type = typeof(T);
for (int i = 0; i < propertyName.Length; i++)
{
PropertyInfo property = type.GetProperty(propertyName[i]);
if (property == null)
throw new ArgumentException("propertyName", "Not Exist");
ParameterExpression param = Expression.Parameter(type, "p");
Expression propertyAccessExpression = Expression.MakeMemberAccess(param, property);
LambdaExpression orderByExpression = Expression.Lambda(propertyAccessExpression, param);
string methodName = ascending[i] ? "OrderBy" : "OrderByDescending";
if (i != 0)
{
methodName = ascending[i] ? "ThenBy" : "ThenByDescending";
}
MethodCallExpression resultExp = Expression.Call(typeof(Queryable), methodName, new Type[] { type, property.PropertyType }, source.Expression, Expression.Quote(orderByExpression));
source = source.Provider.CreateQuery<T>(resultExp);
}
return source;
}
public static IQueryable<T> Where<T>(this IQueryable<T> source, FilterRule[] filterRules) where T : class
{
if (filterRules == null)
{
return source;
}
Type type = typeof(T);
ParameterExpression param = Expression.Parameter(type, "c");
Expression<Func<T, bool>> op = null;
foreach (var rule in filterRules)
{
PropertyInfo property = type.GetProperty(rule.Field);
if (property == null)
{
continue;
}
//c.Field==Value
//c=>c.Field.Contains(Value)
Expression left = Expression.Property(param, property);
Expression right = Expression.Constant(rule.Value);
Type valueType = property.PropertyType;
if (rule.Value == null || rule.Value == "") continue;
DateTime inputDateTime = DateTime.Now;
try
{
if (valueType == typeof(int) || valueType == typeof(int?))
{
right = Expression.Constant(Convert.ToInt32(rule.Value.Split('.')[0]), valueType);
}
else if (valueType == typeof(short) || valueType == typeof(short?))
{
right = Expression.Constant(Convert.ToInt16(rule.Value.Split('.')[0]), valueType);
}
else if (valueType == typeof(byte) || valueType == typeof(byte?))
{
right = Expression.Constant(Convert.ToByte(rule.Value.Split('.')[0]), valueType);
}
else if (valueType == typeof(long) || valueType == typeof(long?))
{
right = Expression.Constant(Convert.ToInt64(rule.Value), valueType);
}
else if (valueType == typeof(float) || valueType == typeof(float?))
{
right = Expression.Constant(Convert.ToSingle(rule.Value), valueType);
}
else if (valueType == typeof(double) || valueType == typeof(double?))
{
right = Expression.Constant(Convert.ToDouble(rule.Value), valueType);
}
else if (valueType == typeof(decimal) || valueType == typeof(decimal?))
{
right = Expression.Constant(Convert.ToDecimal(rule.Value), valueType);
}
else if (valueType == typeof(DateTime) || valueType == typeof(DateTime?))
{
inputDateTime = Convert.ToDateTime(rule.Value);
right = Expression.Constant(Convert.ToDateTime(rule.Value), valueType);
}
else if (valueType == typeof(Guid) || valueType == typeof(Guid?))
{
right = Expression.Constant(Guid.Parse(rule.Value), valueType);
}
else if (valueType == typeof(bool) || valueType == typeof(bool?))
{
right = Expression.Constant(Boolean.Parse(rule.Value), valueType);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
break;
}
Expression filter = Expression.Equal(left, right);
Expression filter2 = null;
MethodInfo method;
switch (rule.Op)
{
case OP.contains:
//BinaryExpression
if (valueType == typeof(string))
{
method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
filter = Expression.Call(left, method, right);
}
else if (valueType == typeof(DateTime) || valueType == typeof(DateTime?))
{
right = Expression.Constant(inputDateTime.Date);
filter = Expression.GreaterThanOrEqual(left, right);
right = Expression.Constant(inputDateTime.Date.AddDays(1));
filter2 = Expression.LessThan(left, right);
}
else
{
filter = Expression.Equal(left, right);
}
break;
case OP.equal:
filter = Expression.Equal(left, right);
break;
case OP.notequal:
filter = Expression.NotEqual(left, right);
break;
case OP.beginwith:
method = typeof(string).GetMethod("StartsWith", new[] { typeof(string) });
filter = Expression.Call(left, method, right);
break;
case OP.endwith:
method = typeof(string).GetMethod("EndsWith", new[] { typeof(string) });
filter = Expression.Call(left, method, right);
break;
case OP.less:
filter = Expression.LessThan(left, right);
break;
case OP.lessorequal:
filter = Expression.LessThanOrEqual(left, right);
break;
case OP.greater:
filter = Expression.GreaterThan(left, right);
break;
case OP.greaterorequal:
filter = Expression.GreaterThanOrEqual(left, right);
break;
default:
break;
}
var lambda = Expression.Lambda<Func<T, bool>>(filter, param);
if (op == null)
{
op = lambda;
}
else
{
op = Expression.Lambda<Func<T, bool>>(Expression.And(op.Body, lambda.Body), op.Parameters);
}
if (filter2 != null)
{
var lambda2 = Expression.Lambda<Func<T, bool>>(filter2, param);
op = Expression.Lambda<Func<T, bool>>(Expression.And(op.Body, lambda2.Body), op.Parameters);
}
}
if (op != null)
{
source = source.Where(op);
}
return source;
}
/// <summary>
/// 多条件过滤
/// </summary>
/// <typeparam name="T">泛型，默认传入类名</typeparam>
/// <param name="source">默认传入where前的IQueryable语句</param>
/// <param name="filters">存放一或多个过滤条件的数组</param>
/// <returns></returns>
public static IQueryable<T> Where<T>(this IQueryable<T> source, Filter[] filters) where T : class
{
//检查过滤条件是否存在，不存在则返回where前的IQueryable语句
if (filters == null)
{
return source;
}
//获取类型
Type type = typeof(T);
ParameterExpression param = Expression.Parameter(type, "c");
Expression<Func<T, bool>> op = null;
foreach (var rule in filters)
{
PropertyInfo property = type.GetProperty(rule.Field);
if (property == null)
{
continue;
}
//c.Field==Value
//c=>(c.Field.Contains(Value) || c.Field.Contains(Value))
Exception outExc = new Exception();
Expression left = Expression.Property(param, property);
Type valueType = property.PropertyType;
if (rule.Value == null || rule.Value.Length <= 0) continue;
Expression<Func<T, bool>> lambdaOut = null;
foreach (var v in rule.Value)
{
if (v == null || v == "") continue;
Expression right = Expression.Constant(v);
DateTime inputDateTime = DateTime.Now;
try
{
if (valueType == typeof(int) || valueType == typeof(int?))
{
right = Expression.Constant(Convert.ToInt32(v.Split('.')[0]), valueType);
}
else if (valueType == typeof(short) || valueType == typeof(short?))
{
right = Expression.Constant(Convert.ToInt16(v.Split('.')[0]), valueType);
}
else if (valueType == typeof(byte) || valueType == typeof(byte?))
{
right = Expression.Constant(Convert.ToByte(v.Split('.')[0]), valueType);
}
else if (valueType == typeof(long) || valueType == typeof(long?))
{
right = Expression.Constant(Convert.ToInt64(v), valueType);
}
else if (valueType == typeof(float) || valueType == typeof(float?))
{
right = Expression.Constant(Convert.ToSingle(v), valueType);
}
else if (valueType == typeof(double) || valueType == typeof(double?))
{
right = Expression.Constant(Convert.ToDouble(v), valueType);
}
else if (valueType == typeof(decimal) || valueType == typeof(decimal?))
{
right = Expression.Constant(Convert.ToDecimal(v), valueType);
}
else if (valueType == typeof(DateTime) || valueType == typeof(DateTime?))
{
inputDateTime = Convert.ToDateTime(v);
right = Expression.Constant(Convert.ToDateTime(v), valueType);
}
else if (valueType == typeof(Guid) || valueType == typeof(Guid?))
{
right = Expression.Constant(Guid.Parse(v), valueType);
}
else if (valueType == typeof(bool) || valueType == typeof(bool?))
{
right = Expression.Constant(Boolean.Parse(v), valueType);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
break;
}
Expression filter = Expression.Equal(left, right);
Expression filter2 = null;
MethodInfo method;
switch (rule.Op)
{
case OP.contains:
//BinaryExpression
if (valueType == typeof(string))
{
method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
filter = Expression.Call(left, method, right);
}
else if (valueType == typeof(DateTime) || valueType == typeof(DateTime?))
{
right = Expression.Constant(inputDateTime.Date);
filter = Expression.GreaterThanOrEqual(left, right);
right = Expression.Constant(inputDateTime.Date.AddDays(1));
filter2 = Expression.LessThan(left, right);
}
else
{
filter = Expression.Equal(left, right);
}
break;
case OP.equal:
filter = Expression.Equal(left, right);
break;
case OP.notequal:
filter = Expression.NotEqual(left, right);
break;
case OP.beginwith:
method = typeof(string).GetMethod("StartsWith", new[] { typeof(string) });
filter = Expression.Call(left, method, right);
break;
case OP.endwith:
method = typeof(string).GetMethod("EndsWith", new[] { typeof(string) });
filter = Expression.Call(left, method, right);
break;
case OP.less:
filter = Expression.LessThan(left, right);
break;
case OP.lessorequal:
filter = Expression.LessThanOrEqual(left, right);
break;
case OP.greater:
filter = Expression.GreaterThan(left, right);
break;
case OP.greaterorequal:
filter = Expression.GreaterThanOrEqual(left, right);
break;
default:
break;
}
var lambda = Expression.Lambda<Func<T, bool>>(filter, param);
if (lambdaOut == null)
{
lambdaOut = lambda;
}
else
{
lambdaOut = Expression.Lambda<Func<T, bool>>(Expression.Or(lambdaOut.Body, lambda.Body), lambdaOut.Parameters);
}
if (filter2 != null)
{
var lambda2 = Expression.Lambda<Func<T, bool>>(filter2, param);
lambdaOut = Expression.Lambda<Func<T, bool>>(Expression.And(lambdaOut.Body, lambda2.Body), lambdaOut.Parameters);
}
}
if (rule.Op == OP.range && rule.Value != null && rule.Value.Length == 2)
{
if (!(rule.Value[0] == null || rule.Value[0] == "") && !(rule.Value[1] == null || rule.Value[1] == ""))
{
Expression right1 = Expression.Constant(rule.Value[0]);
Expression right2 = Expression.Constant(rule.Value[1]);
try
{
if (valueType == typeof(int) || valueType == typeof(int?))
{
right1 = Expression.Constant(Convert.ToInt32(rule.Value[0].Split('.')[0]), valueType);
right2 = Expression.Constant(Convert.ToInt32(rule.Value[1].Split('.')[0]), valueType);
}
else if (valueType == typeof(short) || valueType == typeof(short?))
{
right1 = Expression.Constant(Convert.ToInt16(rule.Value[0].Split('.')[0]), valueType);
right2 = Expression.Constant(Convert.ToInt16(rule.Value[1].Split('.')[0]), valueType);
}
else if (valueType == typeof(byte) || valueType == typeof(byte?))
{
right1 = Expression.Constant(Convert.ToByte(rule.Value[0].Split('.')[0]), valueType);
right2 = Expression.Constant(Convert.ToByte(rule.Value[1].Split('.')[0]), valueType);
}
else if (valueType == typeof(long) || valueType == typeof(long?))
{
right1 = Expression.Constant(Convert.ToInt64(rule.Value[0]), valueType);
right2 = Expression.Constant(Convert.ToInt64(rule.Value[1]), valueType);
}
else if (valueType == typeof(float) || valueType == typeof(float?))
{
right1 = Expression.Constant(Convert.ToSingle(rule.Value[0]), valueType);
right2 = Expression.Constant(Convert.ToSingle(rule.Value[1]), valueType);
}
else if (valueType == typeof(double) || valueType == typeof(double?))
{
right1 = Expression.Constant(Convert.ToDouble(rule.Value[0]), valueType);
right2 = Expression.Constant(Convert.ToDouble(rule.Value[1]), valueType);
}
else if (valueType == typeof(decimal) || valueType == typeof(decimal?))
{
right1 = Expression.Constant(Convert.ToDecimal(rule.Value[0]), valueType);
right2 = Expression.Constant(Convert.ToDecimal(rule.Value[1]), valueType);
}
else if (valueType == typeof(DateTime) || valueType == typeof(DateTime?))
{
right1 = Expression.Constant(Convert.ToDateTime(rule.Value[0]), valueType);
right2 = Expression.Constant(Convert.ToDateTime(rule.Value[1]), valueType);
}
else if (valueType == typeof(Guid) || valueType == typeof(Guid?))
{
right1 = Expression.Constant(Guid.Parse(rule.Value[0]), valueType);
right2 = Expression.Constant(Guid.Parse(rule.Value[0]), valueType);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
break;
}
Expression filter = Expression.GreaterThanOrEqual(left, right1);
Expression filter2 = Expression.LessThanOrEqual(left, right2);
var lambda = Expression.Lambda<Func<T, bool>>(filter, param);
if (lambdaOut == null)
{
lambdaOut = lambda;
}
else
{
lambdaOut = Expression.Lambda<Func<T, bool>>(Expression.Or(lambdaOut.Body, lambda.Body), lambdaOut.Parameters);
}
if (filter2 != null)
{
var lambda2 = Expression.Lambda<Func<T, bool>>(filter2, param);
lambdaOut = Expression.Lambda<Func<T, bool>>(Expression.And(lambdaOut.Body, lambda2.Body), lambdaOut.Parameters);
}
}
}
if (op == null)
{
op = lambdaOut;
}
else
{
if (lambdaOut != null)
{
op = Expression.Lambda<Func<T, bool>>(Expression.And(op.Body, lambdaOut.Body), op.Parameters);
}
}
}
if (op != null)
{
source = source.Where(op);
}
return source;
}
/// <summary>
/// 仅查询string类型数据
/// </summary>
/// <typeparam name="T">泛型，默认传入类名</typeparam>
/// <param name="source">默认传入where前的IQueryable语句</param>
/// <param name="columnNames">存放待查询列名称的数组</param>
/// <param name="filterString">查询内容</param>
/// <returns></returns>
public static IQueryable<T> Where<T>(this IQueryable<T> source, string[] columnNames, string filterString)
{
//获取覆盖当前泛型的类型
Type type = typeof(T);
//构建表达式树的参数c
ParameterExpression param = Expression.Parameter(type, "c");
//构建一个表达式树节点，存放查询内容
Expression right = Expression.Constant(filterString);
//1!=1
//Expression op = Expression.NotEqual(Expression.Constant(1), Expression.Constant(1));
//构建一个存放lamdba表达式树的空对象
Expression<Func<T, bool>> op = null;
//循环遍历存放查询列的数组
foreach (var column in columnNames)
{
//反射获取该列对应属性的类型
PropertyInfo property = type.GetProperty(column);
//如果不存在该属性则结束本次循环，进入下次循环
if (property == null)
{
continue;
}
//c.Field==Value
//c=>c.Field.Contains(Value)
//构建一个表示访问属性的表达式树c=>c.Field
Expression left = Expression.Property(param, property);
//获取属性类型
Type valueType = property.PropertyType;
//若属性类型不为string类型，则结束本次循环
if (valueType != typeof(string)) continue;
//若属性值等于null或字符串长度为0，则结束本次循环
if (filterString == null || filterString == "") continue;
//通过反射获取string类型的Contains方法
MethodInfo method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
//构建一个表示调用参数的方法 c=>c.Field.Contains(Value)
Expression filter = Expression.Call(left, method, right);
//将表达式转换为lambda表达式
var lambda = Expression.Lambda<Func<T, bool>>(filter, param);
if (op == null)
{
//将构建好的lambda表达式赋值给op对象
op = lambda;
}
else
{
// 若op非空，则以or形式追加本次lambda表达式到op对象
op = Expression.Lambda<Func<T, bool>>(Expression.Or(op.Body, lambda.Body), op.Parameters);
}
}
if (op != null)
{
//如果op不为空，则输出合并后的语句
source = source.Where(op);
}
return source;
}
}
}
Filter、OP模型类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace PastureSpace.Models
{
public class FilterRule
{
public string Field { get; set; }
public OP Op { get; set; }
public string Value { get; set; }
}
public class Filter
{
public string Field { get; set; }
public OP Op { get; set; }
public string[] Value { get; set; }
}
public enum OP
{
contains, equal, notequal, beginwith, endwith, less, lessorequal, greater, greaterorequal, range
}
}
pageMode模型类
public class PageModel { public int Page { get; set; } public int Rows { get; set; } public string Sort { get; set; } public string Order { get; set; } public string FilterRules { get; set; } public List<FilterRule> FilterRuleList { get { if (FilterRules == null) return null; return JsonConvert.DeserializeObject<List<FilterRule>>(FilterRules); } } }
ApiResult 模型类
public class ApiResult { public bool success { get; set; } public string msg { get; set; } public object data { get; set; } }
以上代码是从实际项目截取出来的并不是完整的Demo，实际项目测试可行，如有问题请留言。
核心是 从前端element table几个方法获取筛选条件 ， 服务器端where orderby 重载实现多条件筛选和排序。