Как сделать зависимые фильтры в таблице на сайте

В этом примере мы реализовали показ таблицы с зависимыми фильтрами.

Для этого мы:

• создали таблицу
• добавили необходимые колонки (id, country, city, product, created, price)
• для полей country, city реализовали процедуру dict
• настроили процедуру GetItem

CREATE PROCEDURE [dbo].[crud_watch_dependentFilters_country_dict]
	@tableCode nvarchar(32),
	@col nvarchar(32),
	@username nvarchar(32)
as
begin
	select  id Value, name Text
	from tst_regions where parentID is null
end

CREATE PROCEDURE [dbo].[crud_watch_dependentFilters_city_dict]
	@tableCode nvarchar(32),
	@col nvarchar(32),
	@username nvarchar(32),
    @parameters ExtendedDictionaryParameter readonly,
    @filters CRUDFilterParameter readonly
    
as
begin
	declare @country int
	select @country = try_cast(Value as int) from @filters where [Key]='country'
    
	select id Value, name Text
	from tst_regions
	where	parentID is not null 
    and		(isnull(@country, 0) = 0 or parentID = @country)
end

CREATE PROCEDURE [dbo].[crud_watch_dependentFilters_getItems]
	@filters CRUDFilterParameter READONLY,
	@sort sql_variant,
	@direction nvarchar(8),
	@page int,
	@pageSize int,
	@username nvarchar(32)
AS
BEGIN
	declare   @ids TABLE (id int)

	-- filters...
	declare @filterCountry int, @filterCity int
	select @filterCountry = try_cast(Value as int) from @filters where [Key] = 'country'
	select @filterCity = try_cast(Value as int) from @filters where [Key] = 'city'

	insert into @ids
	select id
	from tst_orders
    where	isnull(@filterCountry, 0) = 0 or regionID in (select id from tst_regions where parentID = @filterCountry)
    and		isnull(@filterCity, 0) = 0 or regionID = @filterCity

	-- SELECT 1
	select
    	id id,
		(select name from tst_regions where id  = (select parentID from tst_regions where id = regionID)) country,
		isnull((select name from tst_regions where id  = regionID), '') city,
		(select name from tst_products where id = productID) product,
		isnull(FORMAT(created, 'dd.MM.yyyy'), '01.01.1900') created,
		isnull(price, 0) price
	from tst_orders
	where id in (select id from @ids)
	order by
		case when @sort = '' then created end desc
	OFFSET @PageSize * (@Page - 1) ROWS
	FETCH NEXT @PageSize ROWS ONLY;

	-- SELECT 2
	select count(*) from @ids

	-- SELECT 3
	Select  '  ' Title, 1 HideTitleCount, 1 InstantFilter

END