I am attempting to make a Calendar service, within that calendar service, there are events, and events can be tagged with metadata which is searchable.
I want to be able to search for records where all tags must exists (Mandatory Tags) and/or where any tags exist (Optional Tags).
I have managed to create a query where this works, when the tag value matches 'exactly'. But I cannot work out how to return results where the tag value is LIKE '%value%'.
Here is my current implementation
Tables And Data
CREATE TABLE Events
(
Id INT,
EventText VARCHAR(500)
);
CREATE TABLE EventDates
(
Id INT,
EventId INT,
StartDate DATETIME,
EndDate DATETIME,
Archived BIT
);
CREATE TABLE Tags
(
Id INT,
Description VARCHAR(50)
);
CREATE TABLE EventTags
(
EventId INT,
TagId INT,
Value VARCHAR(50)
);
INSERT INTO Events VALUES (1, 'Event Name 1');
INSERT INTO Events VALUES (2, 'Event Name 2');
INSERT INTO EventDates VALUES (1, 1, '2013-01-01', '2013-01-02', 0);
INSERT INTO EventDates VALUES (2, 1, '2013-01-07', '2013-01-08', 0);
INSERT INTO EventDates VALUES (3, 2, '2013-01-02', '2013-01-03', 0);
INSERT INTO Tags VALUES (1, 'Tag Name 1');
INSERT INTO Tags VALUES (2, 'Tag Name 2');
INSERT INTO EventTags VALUES (1, 1, 'Value 1');
INSERT INTO EventTags VALUES (1, 1, 'Value 2');
INSERT INTO EventTags VALUES (1, 2, 'Value 1');
INSERT INTO EventTags VALUES (1, 2, 'Value 2');
INSERT INTO EventTags VALUES (2, 1, 'Value 1');
Query
DECLARE @MandatoryTagXml XML
DECLARE @OptionalTagXml XML
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @SearchTypeId SMALLINT
SET @StartDate = '2013-01-01'
SET @EndDate = '2013-01-31'
SET @SearchTypeId = 1
-- Tags that it must match all of
SET @MandatoryTagXml = '<tags>
<tag>
<description>Tag Name 1</description>
<value>Value 1</value>
</tag>
</tags>'
-- Tags that it can match one or more of
SET @OptionalTagXml = '<tags>
<tag>
<description>Tag Name 2</description>
<value>Value 2</value>
</tag>
</tags>'
DECLARE @MandatoryIdTable TABLE ([EventId] BIGINT, [EventDateId] BIGINT)
DECLARE @OptionalIdTable TABLE ([EventId] BIGINT, [EventDateId] BIGINT)
IF(@MandatoryTagXml IS NOT NULL)
BEGIN
-- Select ids with matching mandatory tags.
;WITH MandatoryTags AS
(
SELECT TagValue.value('(./value)[1]', 'nvarchar(100)') AS value,
TagValue.value('(./description)[1]', 'nvarchar(100)') AS [description]
FROM @MandatoryTagXml.nodes('/tags/tag') AS T(TagValue)
)
INSERT INTO @MandatoryIdTable
-- Records where ALL tags match EXACTLY
SELECT E.Id [EventId], ED.Id [EventDateId]
FROM [dbo].[Events] E
INNER JOIN [dbo].[EventDates] ED ON ED.EventId = E.Id
WHERE ED.StartDate >= @StartDate
AND ED.EndDate <= @EndDate
AND ED.Archived = 0
AND NOT EXISTS (
SELECT T.Id, c.value
FROM MandatoryTags c JOIN Tags T
ON c.[description] = T.[Description]
EXCEPT
SELECT T.TagId, T.Value
FROM [EventTags] T
WHERE T.EventId = E.Id
)
END
ELSE -- Select All records
BEGIN
INSERT INTO @MandatoryIdTable
-- Records where ALL tags match EXACTLY
SELECT E.Id [EventId], ED.Id [EventDateId]
FROM [dbo].[Events] E
INNER JOIN [dbo].[EventDates] ED ON ED.EventId = E.Id
WHERE ED.StartDate >= @StartDate
AND ED.EndDate <= @EndDate
AND ED.Archived = 0
END
;WITH OptionalTags AS
(
SELECT TagValue.value('(./value)[1]', 'nvarchar(100)') AS value,
TagValue.value('(./description)[1]', 'nvarchar(100)') AS [description]
FROM @OptionalTagXml.nodes('/tags/tag') AS T(TagValue)
)
INSERT INTO @OptionalIdTable
-- Records ANY tags match EXACTLY
SELECT E.Id [EventId], ED.Id [EventDateId]
FROM [dbo].[Events] E
INNER JOIN [dbo].[EventDates] ED ON ED.EventId = E.Id
WHERE ED.StartDate >= @StartDate
AND ED.EndDate <= @EndDate
AND ED.Archived = 0
AND EXISTS (
SELECT T.Id, c.value
FROM OptionalTags c JOIN Tags T
ON c.[description] = T.[Description]
INTERSECT
SELECT T.TagId, T.Value
FROM [EventTags] T
WHERE T.EventId = E.Id
)
-- Determine if we need to factor in optional tags in result set
IF (@OptionalTagXml IS NOT NULL)
BEGIN
-- Select results that exist in both optional and mandatory tables
SELECT DISTINCT M.*
FROM @MandatoryIdTable M
INNER JOIN @OptionalIdTable O ON O.EventId = M.EventId AND O.EventDateId = M.EventDateId
END
ELSE
BEGIN
-- Select results that exist in mandatory table
SELECT DISTINCT M.*
FROM @MandatoryIdTable M
END
I have created an SQLFiddle Demo for it.
My idea is to use @SearchTypeId to switch between exact match searching and LIKE match searching.
(Note I am not a DBA, so there may be better ways to do this. I am open to suggestions)
Can anyone offer suggestions as to how to get LIKE matches on tag values?
Many thanks
I think your idea of using some type of flag/switch to change the matching type will work. I implemented it using words instead of IDs, but if you just toggle the join condition based on the search type, you should get LIKE matching as expected.
Fiddle: http://sqlfiddle.com/#!3/d9fbd/3/0
I first added a tag that was similar to tag 1 and attached it to event 2 for testing.
INSERT INTO Tags VALUES (3, 'Different Tag Name 1');
INSERT INTO EventTags VALUES (2, 3, 'Value 3');
I then created the search type flag/switch.
DECLARE @SearchType NVARCHAR(10)
SET @SearchType = 'LIKE' --other type is EXACT
So now you can toggle the EXISTS join condition based on that flag. I changed your NOT EXISTS to EXISTS just for my understanding. Below is the new join condition, using the mandatory tag block as the example.
-- Select ids with matching mandatory tags.
;WITH MandatoryTags AS
(
SELECT TagValue.value('(./value)[1]', 'nvarchar(100)') AS value,
TagValue.value('(./description)[1]', 'nvarchar(100)') AS [description]
FROM @MandatoryTagXml.nodes('/tags/tag') AS T(TagValue)
)
INSERT INTO @MandatoryIdTable
-- Records where ALL tags match EXACTLY or LIKE
SELECT E.Id [EventId], ED.Id [EventDateId]
FROM [dbo].[Events] E
INNER JOIN [dbo].[EventDates] ED ON ED.EventId = E.Id
WHERE ED.StartDate >= @StartDate
AND ED.EndDate <= @EndDate
AND ED.Archived = 0
AND EXISTS (
-- Just care about tag IDs here, not the values
SELECT T.Id
FROM MandatoryTags c JOIN Tags T
ON (
-- Toggle join type based on flag/switch
(@SearchType = 'EXACT' AND c.[description] = T.[Description])
OR
(@SearchType = 'LIKE' AND T.[Description] LIKE ('%' + c.[description] + '%'))
)
INTERSECT
SELECT T.TagId
FROM [EventTags] T
WHERE T.EventId = E.Id
)
I'm sure there is some re-factoring and optimization you can do in this SQL, but this should at least give you one idea on how to do LIKE matching if desired. Hope it helps!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With