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» (отфильтрованные соответственно), а затем поворачивая данные в основной массив (если возвращены не все данные, я знаю, что фильтр «отфильтровал» эту строку, и строка была удалена из массива).

Это очень неэффективно, так как занимает много времени и открывает разные подключения к базе данных для каждой строки. Поэтому я смотрю, есть ли способ уменьшить количество соединений с базой данных и / или сложность алгоритма.

Я хочу сделать это в идеале, каким-то образом создать представление базы данных, которое содержит все динамические поля, с соответствующими индексами и т. Д., А затем запустить отчет против этого. Это кажется самым чистым способом, но не уверен, как я бы динамически создавал такое представление. Также не уверен, как это влияет на производительность.

Может ли кто-нибудь предоставить какое-либо понимание или мнение о том, как реализовать это решение, или альтернативное, лучшее решение? Я дергал себя за волосы, чтобы создать стабильное, эффективное решение, и я не могу поверить, что это никогда не было сделано раньше. Заранее спасибо!

0

Решение

Первый 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
0

Другие решения

Хотя я и предложил отойти от 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;

Пример по SQL Fiddle

Я думаю, что то, что вы упомянули в комментарии, может быть способом продвижения вперед, вы, безусловно, должны хранить как можно больше основных полей в основной таблице, как вы сделали это с contactNumber и contactName, а затем просто использовать таблицу атрибутов для внешнего поля, которые могут быть добавлены на лету.

0

По вопросам рекламы [email protected]