sql server 2008 - SQL SELECT where tag value LIKE -
i attempting make calendar service, within calendar service, there events, , events can tagged metadata searchable.
i want able search records tags must exists (mandatory tags) and/or tags exist (optional tags).
i have managed create query works, when tag value matches 'exactly'. cannot work out how return results tag value '%value%'.
here current implementation
tables , 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 events values (1, 'event name 1'); insert events values (2, 'event name 2'); insert eventdates values (1, 1, '2013-01-01', '2013-01-02', 0); insert eventdates values (2, 1, '2013-01-07', '2013-01-08', 0); insert eventdates values (3, 2, '2013-01-02', '2013-01-03', 0); insert tags values (1, 'tag name 1'); insert tags values (2, 'tag name 2'); insert eventtags values (1, 1, 'value 1'); insert eventtags values (1, 1, 'value 2'); insert eventtags values (1, 2, 'value 1'); insert eventtags values (1, 2, 'value 2'); insert 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 must match of set @mandatorytagxml = '<tags> <tag> <description>tag name 1</description> <value>value 1</value> </tag> </tags>' -- tags can match 1 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 not null) begin -- select ids matching mandatory tags. ;with mandatorytags ( select tagvalue.value('(./value)[1]', 'nvarchar(100)') value, tagvalue.value('(./description)[1]', 'nvarchar(100)') [description] @mandatorytagxml.nodes('/tags/tag') t(tagvalue) ) insert @mandatoryidtable -- records tags match select e.id [eventid], ed.id [eventdateid] [dbo].[events] e inner join [dbo].[eventdates] ed on ed.eventid = e.id ed.startdate >= @startdate , ed.enddate <= @enddate , ed.archived = 0 , not exists ( select t.id, c.value mandatorytags c join tags t on c.[description] = t.[description] except select t.tagid, t.value [eventtags] t t.eventid = e.id ) end else -- select records begin insert @mandatoryidtable -- records tags match select e.id [eventid], ed.id [eventdateid] [dbo].[events] e inner join [dbo].[eventdates] ed on ed.eventid = e.id ed.startdate >= @startdate , ed.enddate <= @enddate , ed.archived = 0 end ;with optionaltags ( select tagvalue.value('(./value)[1]', 'nvarchar(100)') value, tagvalue.value('(./description)[1]', 'nvarchar(100)') [description] @optionaltagxml.nodes('/tags/tag') t(tagvalue) ) insert @optionalidtable -- records tags match select e.id [eventid], ed.id [eventdateid] [dbo].[events] e inner join [dbo].[eventdates] ed on ed.eventid = e.id ed.startdate >= @startdate , ed.enddate <= @enddate , ed.archived = 0 , exists ( select t.id, c.value optionaltags c join tags t on c.[description] = t.[description] intersect select t.tagid, t.value [eventtags] t t.eventid = e.id ) -- determine if need factor in optional tags in result set if (@optionaltagxml not null) begin -- select results exist in both optional , mandatory tables select distinct m.* @mandatoryidtable m inner join @optionalidtable o on o.eventid = m.eventid , o.eventdateid = m.eventdateid end else begin -- select results exist in mandatory table select distinct m.* @mandatoryidtable m end i have created sqlfiddle demo it.
my idea use @searchtypeid switch between exact match searching , match searching.
(note not dba, there may better ways this. open suggestions)
can offer suggestions how matches on tag values?
many thanks
i think idea of using type of flag/switch change matching type work. implemented using words instead of ids, if toggle join condition based on search type, should matching expected.
fiddle: http://sqlfiddle.com/#!3/d9fbd/3/0
i first added tag similar tag 1 , attached event 2 testing.
insert tags values (3, 'different tag name 1'); insert eventtags values (2, 3, 'value 3'); i created search type flag/switch.
declare @searchtype nvarchar(10) set @searchtype = 'like' --other type exact so can toggle exists join condition based on flag. changed not exists exists understanding. below new join condition, using mandatory tag block example.
-- select ids matching mandatory tags. ;with mandatorytags ( select tagvalue.value('(./value)[1]', 'nvarchar(100)') value, tagvalue.value('(./description)[1]', 'nvarchar(100)') [description] @mandatorytagxml.nodes('/tags/tag') t(tagvalue) ) insert @mandatoryidtable -- records tags match or select e.id [eventid], ed.id [eventdateid] [dbo].[events] e inner join [dbo].[eventdates] ed on ed.eventid = e.id ed.startdate >= @startdate , ed.enddate <= @enddate , ed.archived = 0 , exists ( -- care tag ids here, not values select t.id mandatorytags c join tags t on ( -- toggle join type based on flag/switch (@searchtype = 'exact' , c.[description] = t.[description]) or (@searchtype = 'like' , t.[description] ('%' + c.[description] + '%')) ) intersect select t.tagid [eventtags] t t.eventid = e.id ) i'm sure there re-factoring , optimization can in sql, should @ least give 1 idea on how matching if desired. hope helps!
Comments
Post a Comment