Grid Filter Row Large Data Source
When we have a large data source and the amount of possible items in a filter dropdown is too big, loading all the items in the filter dropdowns on each grid load is not an option.
For example if "Food" dropdown has 1 million items it would be impractical to load them all.
One easy solution would be to use a textbox instead, like we've done for the Person column.
Another option is to use a dropdown with remote search, as done for the Food column, initially only a few items are loaded, and additional items are loaded when we make a search (
Country, Chef and Meals also use
So if you select a certain "Food" and a certain "Country" and after search in the "Meals" editor, the result will contain only meals that have rows with the selected "Food" and "Country" (Meals also gets filtered by previously selected meals).
On the initial load only the filter dropdowns items for the selected value(s) are received from the Grid Model.
For example if "Food" dropdown has 1 million items it would be impractical to load them all.
One easy solution would be to use a textbox instead, like we've done for the Person column.
Another option is to use a dropdown with remote search, as done for the Food column, initially only a few items are loaded, and additional items are loaded when we make a search (
SearchFunc is used for this).
We've set o-nfload on the country dropdown so it won't load each time the grid loads,
this is only necessary for editors that get their data from the grid model (Tag.frow set in the grid action).Country, Chef and Meals also use
SearchFunc like "Country" except grid filter parameters (order, selected values)
are also sent to the searchFunc so the resulting items are filtered based on the previous selected filters.So if you select a certain "Food" and a certain "Country" and after search in the "Meals" editor, the result will contain only meals that have rows with the selected "Food" and "Country" (Meals also gets filtered by previously selected meals).
On the initial load only the filter dropdowns items for the selected value(s) are received from the Grid Model.
GridFilterRowServerSideData/LargeDataSource.cshtml
@{
var foodFltOpt = new DropdownListOpt
{
Name = "Food",
Url = Url.Action("GetFoodsInit")
}
.FilterNoReload()
.RemoteSearch(Url.Action("SearchFood"))
.FoodImgItem();
}
@(Html.Awe().Grid("GridFrowLargeData")
.Height(390)
.Reorderable()
.Resizable()
.Url(Url.Action("LunchFilterGridLarge", "GridFilterRowServerSideData"))
.Mod(o => o.Main().FilterRow())
.Columns(
new Column { Bind = "Id", Width = 100 }
.FltFormat("search:"),
new Column { Bind = "Person" }
.FltString(),
new Column { Bind = "Food.Name", ClientFormatFunc = "site.imgFood", MinWidth = 200 }
.FltDropdownList(foodFltOpt),
new Column { Bind = "Country.Name", Header = "Country" }
.FltDropdownList(new DropdownListOpt { Name = "Country" }.RemoteSearch(Url.Action("SearchCountry"))),
new Column { Prop = "MealsStr", MinWidth = 200, Header = "Meals", Grow = 1.7 }
.FltMultiselect(new MultiselectOpt { Name = "Meals" }.RemoteSearch(Url.Action("SearchMeals"))),
new Column { Bind = "Chef.FirstName,Chef.LastName", Prop = "ChefName", Header = "Chef" }
.FltDropdownList(new DropdownListOpt { Name = "Chef" }.RemoteSearch(Url.Action("SearchChef")))
))
Demos/Grid/GridFilterRowServerSideDataController.cs
public IActionResult LunchFilterGridLarge(GridParams g, LunchFilter filter)
{
var query = Db.Lunches.AsQueryable();
var filterBuilder = new FilterBuilder<Lunch>();
setupLunchFilter(filterBuilder, query, filter);
query = filterBuilder.ApplyAsync().Result;
var gmb = new GridModelBuilder<Lunch>(query, g)
{
KeyProp = o => o.Id,
Map = MapToGridModel,
Tag = filterBuilder.GetGridData()
};
return Json(gmb.Build());
}
private void setupLunchFilter(
FilterBuilder<Lunch> filterBuilder,
IQueryable<Lunch> query,
LunchFilter filter)
{
filterBuilder.Query = query;
filterBuilder.Filter = filter;
filterBuilder
.StringFilter(nameof(LunchFilter.Person))
// Food filter dropdown is not using the grid filter query to get data
// so it is possible to select an item from this filter dropdown
// that will give us zero results in the grid (if there are other filters set)
.Add(nameof(LunchFilter.Food), new FilterRule<Lunch> { Query = itm => itm.Food.Id == filter.Food })
// returning data for the selected filter values
.Add(nameof(LunchFilter.Country),
new FilterRule<Lunch>
{
Query = itm => itm.Country.Id == filter.Country,
GetData = async q =>
{
if (filter.Country == null) return null;
return (q.Select(o => o.Country).Where(o => o.Id == filter.Country).Distinct().ToArray())
.Select(o => new KeyContent(o.Id, o.Name));
}
})
.Add(nameof(LunchFilter.Meals),
new FilterRule<Lunch>
{
QueryFunc = () =>
{
var ids = filter.Meals;
var count = ids.Count();
return itm => itm.Meals.Count(m => ids.Contains(m.Id)) == count;
},
GetData = async q =>
{
if (filter.Meals == null) return null;
return (q.SelectMany(o => o.Meals).Where(o => filter.Meals.Contains(o.Id)).Distinct().ToArray())
.OrderBy(o => o.Id)
.Select(o => new KeyContent(o.Id, o.Name));
},
// get data after "Meals" QueryFunc has been applied to the Query (q)
SelfQuery = true
})
.Add(nameof(LunchFilter.Chef),
new FilterRule<Lunch>
{
Query = itm => itm.Chef.Id == filter.Chef,
GetData = async q =>
{
if (filter.Chef == null) return null;
return (q.Select(lunch => lunch.Chef)
.Where(chef => chef.Id == filter.Chef)
.Take(1)
.ToArray())
.Select(o => new KeyContent(o.Id, o.FullName));
}
});
}
public IActionResult SearchFood(string term)
{
var items = (Db.Foods
.AsQueryable()
.Filter(term, o => o.Name)
.Take(10)
.ToArray())
.Select(o => new MealDisplay(o.Id, o.Name, o.Pic));
return Json(items);
}
public IActionResult GetFoodsInit(int? v)
{
var query = Db.Foods.OrderBy(o => o.Id);
var items = query.Take(3).ToList();
var selected = query.FirstOrDefault(o => o.Id == v);
if (selected != null && !items.Contains(selected))
{
items.Add(selected);
}
var lst = new List<MealDisplay> { new MealDisplay("", "any", "pasta.png") };
lst.AddRange(items.Select(o => new MealDisplay(o.Id, o.Name, o.Pic)));
return Json(lst);
}
public IActionResult SearchCountry(LunchFilter filter, string term)
{
var query = Db.Lunches.AsQueryable();
var fb = new FilterBuilder<Lunch>();
setupLunchFilter(fb, query, filter);
query = fb.GetQueryFor(nameof(LunchFilter.Country));
var items = (query.Select(o => o.Country)
.Filter(term, o => o.Name)
.Distinct()
.Take(10)
.ToArray())
.Select(o => new KeyContent(o.Id, o.Name));
return Json(items);
}
public IActionResult SearchMeals(string term, LunchFilter filter)
{
var query = Db.Lunches.AsQueryable();
var fb = new FilterBuilder<Lunch>();
// apply other grid filters in order
setupLunchFilter(fb, query, filter);
query = fb.GetQueryFor(nameof(LunchFilter.Meals));
var data = (query.SelectMany(o => o.Meals)
.Filter(term, o => o.Name)
.Distinct()
.Take(10)
.OrderBy(o => o.Id)
.ToArray())
.Select(o => new KeyContent(o.Id, o.Name));
return Json(data);
}
public IActionResult SearchChef(string term, LunchFilter fp)
{
var query = Db.Lunches.AsQueryable();
var fb = new FilterBuilder<Lunch>();
setupLunchFilter(fb, query, fp);
query = fb.GetQueryFor(nameof(LunchFilter.Chef));
var data = (query.Select(o => o.Chef)
.Filter(term, o => o.FirstName, o => o.LastName)
.Distinct()
.Take(10)
.OrderBy(o => o.Id)
.ToArray())
.Select(o => new KeyContent(o.Id, o.FullName));
return Json(data);
}
#endregion
}
}
Comments