Как создать вложенные таблицы на сайте

В этом примере мы реализовали подтаблицы (вложенные таблицы).

Для этого мы:

  • создали таблицу
  • добавили необходимые колонки (id, name, fio, countOrders, sumOrders)
  • настроили GetItem, где для отображения подтаблицы добавили колонку sub_{имя колонки}(в колонки таблицы это поле добавлять не нужно)
  • в таблице используемой как подтаблица (в нашем случае watch_subTables) настраиваем GetItem 
  • в последней подтаблице (watch_createSearch) также настраиваем GetItem
CREATE PROCEDURE [dbo].[crud_watch_table_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 @filterItemID nvarchar(128)
	select @filterItemID = try_cast(Value as int) from @filters where [Key] = 'itemID'
	
	insert into @ids
	select id
	from tst_customers
	where (isnull(@filterItemID, 0)= 0 or id = @filterItemID)
	
	-- SELECT 1
	select 
		id id,
		isnull(name, '') name,       
		isnull(fio, '') fio,
        (select count(*) from tst_orders where customerID = c.id) countOrders,
        isnull((select sum(price) from tst_orders where customerID = c.id), 0) sumOrders
	from tst_customers c
	where id in (select id from @ids)
	order by  
		case when @sort = ''  then id end asc,
        case when @sort = 'name' and @direction = 'up' then name end asc,
        case when @sort = 'name' and @direction = 'down' then name end desc,
        case when @sort = 'fio' and @direction = 'up' then fio end asc,
        case when @sort = 'fio' and @direction = 'down' then fio end desc
	OFFSET @PageSize * (@Page - 1) ROWS
	FETCH NEXT @PageSize ROWS ONLY;
	
	-- SELECT 2
	select count(*) from @ids	

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

--ru 30.05.2021 10:59:26
CREATE PROCEDURE [dbo].[crud_watch_subTables_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 @filterItemID int
	select @filterItemID = try_cast(Value as int) from @filters where [Key] = 'itemID'
	
	insert into @ids
	select id
	from tst_orders
    where isnull(@filterItemID, 0) = 0 or customerID = @filterItemID
	
	-- SELECT 1
	select 
		id id,
		'' product,
        '
' sub_product,
		FORMAT(created, 'dd.MM.yyyy') created,
		(select name from tst_statuses where id = o.id) status,
		isnull(price, 0) price
	from tst_orders o
	where id in (select id from @ids)
	order by  
    	case when @sort = '' then created end desc,
		case when @sort = 'created' and @direction = 'down' then created end desc,
		case when @sort = 'created' and @direction = 'up' then created end asc,
        case when @sort = 'price' and @direction = 'down' then price end desc,
		case when @sort = 'price' and @direction = 'up' then price end asc
	OFFSET @PageSize * (@Page - 1) ROWS
	FETCH NEXT @PageSize ROWS ONLY;
	
	-- SELECT 2
	select count(*) from @ids	

	-- SELECT 3
	Select  '  ' Title, 1 HideTitleCount
END
CREATE PROCEDURE [dbo].[crud_watch_createSearch_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 @filterItemID nvarchar(128)
	select @filterItemID = try_cast(Value as int) from @filters where [Key] = 'itemID'

	insert into @ids
	select id
	from tst_products
	where (isnull(@filterItemID, 0) = 0 or id = @filterItemID)

	-- SELECT 1
	select
		id id,
		isnull(name, '') name,
        isnull(art, 'Не указано') art,
		isnull([desc], 'Не указано') [desc],
        isnull(try_convert(datetime, created, 104), '')created,
        isnull(isVisible, 0) isVisible,
        isnull((select name from tst_categories where id = (select categoryID from tst_categoryProducts where productID = p.id)), 'Не указано') category
	from tst_products p
	where id in (select id from @ids)
	order by
		case when @sort = ''  then id end asc,
        case when @sort = 'name' and @direction = 'up' then name end asc,
        case when @sort = 'name' and @direction = 'down' then name end desc,
        case when @sort = 'price' and @direction = 'up' then price end asc,
        case when @sort = 'price' and @direction = 'down' then price end desc,
        case when @sort = 'desc' and @direction = 'up' then [desc] end asc,
        case when @sort = 'desc' and @direction = 'down' then [desc] end desc,
        case when @sort = 'created' and @direction = 'up' then created end asc,
        case when @sort = 'created' and @direction = 'down' 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, iif(@filterItemID is null, 1, 0) FastCreate, 1 FastCreateSearch
END

Другие модули

Последние обновления

SQL-инструмент для создания личных кабинетов на сайте

Суть подхода и история создания Falcon Space

Платформа Falcon Space

Это снижение стоимости владения

за счет меньшего количества людей для поддержки

Это быстрое внесение изменений

по ходу эксплуатации программы

Это современный интерфейс

полная адаптация под мобильные устройства