Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL SELECT where tag value LIKE

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

like image 810
Yetiish Avatar asked Aug 16 '13 09:08

Yetiish


1 Answers

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!

like image 171
chucknelson Avatar answered Sep 28 '22 21:09

chucknelson