T-SQL Filter на динамических столбцах
Вероятно, об этом уже спрашивали, но это такая сложная тема, что у меня возникают проблемы с обдумыванием. Так думал, я бы спросил конкретику.
У меня есть следующая таблица «CampaignCalls», например:
ID | contactName | contactNumber
1 | Joe Bloggs | 123456789
2 | Simon Smith | 456987321
3 | Jane Doe | 852936414
Кроме того, у меня есть таблица «пользовательские столбцы» «один ко многим», которые определяются пользователем, «CampaignCall_Fields», например:
ID | fieldName
1 | Company
2 | Alternative Number
3 | Address 1
4 | Address 2
И таблица пересечений, которая определяет каждое соответствующее значение для пользовательского столбца («CampaignCall_Field_values»), например:
CampaignCall_ID | field_ID | value
1 | 1 | ACME
1 | 2 | 789456123
1 | 3 | 123 Fake St
1 | 4 | London
2 | 1 | Initech
2 | 2 | 789456123
2 | 3 | 456 Fake St
2 | 4 | Paris
3 | 1 | Greendale
3 | 2 | 789456123
3 | 3 | 789 Fake St
3 | 4 | New York City
У меня есть приложение, которое должно отображать пользователям отчет обо всех строках, например, в следующем формате:
Name | Number | Company | Address 1 | Address 2
Joe Bloggs | 123456789 | ACME | 123 Fake St | London
Simon Smith | 456987321 | Initech | 456 Fake St | Paris
Но я также хочу дать пользователям возможность создавать фильтры для любого из столбцов, указанных в этом примере, например, пользователь может сказать «возвращать только те строки, где Name = Joe Bloggs AND Company = ACME».
В НАСТОЯЩЕЕ ВРЕМЯ Я делаю это путем извлечения всех данных (соответственно отфильтрованных) из таблицы «CampaignCalls», а затем, в PHP, перебирая все возвращенные строки и извлекая данные из таблицы «CampaignCall_Field_values» (отфильтрованные соответственно), а затем поворачивая данные в основной массив (если возвращены не все данные, я знаю, что фильтр «отфильтровал» эту строку, и строка была удалена из массива).
Это очень неэффективно, так как занимает много времени и открывает разные подключения к базе данных для каждой строки. Поэтому я смотрю, есть ли способ уменьшить количество соединений с базой данных и / или сложность алгоритма.
Я хочу сделать это в идеале, каким-то образом создать представление базы данных, которое содержит все динамические поля, с соответствующими индексами и т. Д., А затем запустить отчет против этого. Это кажется самым чистым способом, но не уверен, как я бы динамически создавал такое представление. Также не уверен, как это влияет на производительность.
Может ли кто-нибудь предоставить какое-либо понимание или мнение о том, как реализовать это решение, или альтернативное, лучшее решение? Я дергал себя за волосы, чтобы создать стабильное, эффективное решение, и я не могу поверить, что это никогда не было сделано раньше. Заранее спасибо!
Решение
Первый join три таблицы, чтобы получить соответствующие данные, а затем использовать pivot чтобы получить необходимый формат
SELECT *
FROM (SELECT contactName,
value,
fieldName
FROM CampaignCalls C
JOIN CampaignCall_Field_values CF
ON c.ID = cf.CampaignCall_ID
JOIN [custom columns] Cs
ON cs.ID = cf.field_ID)A
PIVOT (Max(value)
FOR fieldname IN ([Company],
[Alternative Number],
[Address 1],
[Address 2]))piv
Обновить: Если вы не знаете fields затем используйте Dynamic Pivot
DECLARE @sql NVARCHAR(max),
@cols VARCHAR(max)
SET @cols = (SELECT DISTINCT Quotename(fieldName) + ','
FROM [custom columns]
FOR xml path(''))
SELECT @cols = LEFT(@cols, Len(@cols) - 1)
SET @sql='SELECT *
FROM (SELECT contactName,
value,
fieldName
FROM CampaignCalls C
JOIN CampaignCall_Field_values CF
ON c.ID = cf.CampaignCall_ID
JOIN [custom columns] Cs
ON cs.ID = cf.field_ID)A
PIVOT (Max(value)
FOR fieldname IN (' + @cols
+ '))piv '
EXEC Sp_executesql @sql
Другие решения
Хотя я и предложил отойти от EAV в комментариях, я все же постараюсь помочь с проблемой, которую он принес. Я бы посоветовал применить любой фильтр перед тем, как повернуть данные, чтобы сделать это эффективно, вам сначала нужно будет создать новый тип:
CREATE TYPE dbo.StringPair AS TABLE
(
Value1 NVARCHAR(MAX) NOT NULL,
Value2 NVARCHAR(MAX) NOT NULL
);
Это должно содержать пары, необходимые для вашего фильтра (имя поля и значение поля), тогда вы можете создать хранимую процедуру, которая принимает это как параметр:
CREATE PROCEDURE dbo.GetCampaignCalls @Filter dbo.StringPair READONLY
AS
BEGIN
DECLARE @Cols NVARCHAR(MAX) = STUFF((SELECT ',' + QUOTENAME(FieldName)
FROM CampaignCall_Fields
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '');
DECLARE @SQL NVARCHAR(MAX) = '
WITH CampaignCallFields AS
( SELECT cc.ID,
cc.contactName,
cc.contactNumber,
f.FieldName,
v.Value
FROM CampaignCalls AS cc
INNER JOIN CampaignCall_Field_values AS v
ON cc.ID = v.CampaignCall_ID
INNER JOIN CampaignCall_Fields AS f
ON f.ID = v.field_ID
)
SELECT pvt.*
FROM ( SELECT *
FROM CampaignCallFields AS c
WHERE EXISTS
( SELECT 1
FROM CampaignCallFields AS c2
INNER JOIN @Filter AS f
ON f.Value1 = c2.FieldName
AND f.Value2 = c2.Value
WHERE c2.ID = c.ID
GROUP BY c2.ID
HAVING COUNT(*) = (SELECT COUNT(*) FROM @Filter)
)
) AS c
PIVOT
( MAX(Value)
FOR FieldName IN (' + @Cols + ')
) AS pvt;';
EXECUTE sp_executesql @SQL, N'@Filter dbo.StringPair READONLY', @Filter;
END
Это тогда будет называться как:
DECLARE @Filter dbo.StringPair;
INSERT @Filter VALUES ('Company', 'ACME');
EXECUTE dbo.GetCampaignCalls @Filter;
Я думаю, что то, что вы упомянули в комментарии, может быть способом продвижения вперед, вы, безусловно, должны хранить как можно больше основных полей в основной таблице, как вы сделали это с contactNumber и contactName, а затем просто использовать таблицу атрибутов для внешнего поля, которые могут быть добавлены на лету.