SELECT related articles based on keywords from sin

Question

I am working on a web project which shows article details on one of it pages and on side i also need to show top 5 related article based on keywords or tags.

I am not sure how i can entirely do it using T-SQL rather than doing part of processing from code behind.

I using FUNCTION to split my keywords and pass results to other query to get desired result which is not working for me.

CREATE TABLE Article
(
 ArticleID int,
 Title varchar(200),
 Description varchar(500),
 Details nvarchar(MAX),
 keywords varchar(100)
 )

INSERT INTO Article VALUES(1, 'Article One','Article desc', 'article details', 'one,two,three')
INSERT INTO Article VALUES(2, 'Article Two','Article desc', 'article details', 'two,three,four')
INSERT INTO Article VALUES(3, 'Article three','Article desc', 'article details', 'three,four,five')
INSERT INTO Article VALUES(4, 'Article four','Article desc', 'article details', ',four,five,six')
INSERT INTO Article VALUES(5, 'Article five','Article desc', 'article details', 'two,three')
INSERT INTO Article VALUES(6, 'Article six','Article desc', 'article details', 'eight, nine')
INSERT INTO Article VALUES(7, 'Article six','Article desc', 'article details', 'ten, nine')
INSERT INTO Article VALUES(8, 'Article six','Article desc', 'article details', 'eleven, eight')

FUNCTION

CREATE FUNCTION [dbo].[uf_SplitKeywords] 
   (  @DELIMITER VARCHAR(5), 
      @LIST      VARCHAR(MAX) 
   ) 
   RETURNS @TABLEOFVALUES TABLE 
      (  ROWID   SMALLINT IDENTITY(1,1), 
         [VALUE] VARCHAR(MAX) 
      ) 
AS 
   BEGIN

      DECLARE @LENSTRING INT 

      WHILE LEN( @LIST ) > 0 
         BEGIN 

            SELECT @LENSTRING = 
               (CASE CHARINDEX( @DELIMITER, @LIST ) 
                   WHEN 0 THEN LEN( @LIST ) 
                   ELSE ( CHARINDEX( @DELIMITER, @LIST ) -1 )
                END
               ) 

            INSERT INTO @TABLEOFVALUES 
               SELECT SUBSTRING( @LIST, 1, @LENSTRING )

            SELECT @LIST = 
               (CASE ( LEN( @LIST ) - @LENSTRING ) 
                   WHEN 0 THEN '' 
                   ELSE RIGHT( @LIST, LEN( @LIST ) - @LENSTRING - 1 ) 
                END
               ) 
         END

      RETURN 

   END

What i need?

Show article with id 3

SELECT ArticleID, Title, Keywords FROM Article WHERE ArticleID = 3

Then i need to show related article based on keywords three,four,five From selected article as in this case should be article with articleid=3 in this case result should show me article with id 1,2,3,4,5 as keywords match with these rows only.

I am trying to achieve this with following query which is not working

SELECT TOP 5 ArticleID, Title, Keywords FROM Articles WHERE Keywords IN
(SELECT '''%'+ VALUE+ '%''' AS VALUE FROM [uf_SplitKeywords] (',', 'one,two,three'))

I would appreciate help in this regard.

Sample on sqlFiddle for some reason i am not able to create FUNCTION which i have mentioned above on sqlFiddle.

Answer 1

Alter FUNCTION [dbo].[uf_SplitKeywords] 
   (  @DELIMITER VARCHAR(5), 
      @LIST      VARCHAR(MAX) 
   ) 
   RETURNS @TABLEOFVALUES TABLE 
      (  ROWID   int IDENTITY(1,1), 
         [VALUE] VARCHAR(MAX) 
      ) 
AS 
   BEGIN
   Declare @Pos int
   While LEN(@List) > 0
      begin
        Select @Pos=CHARINDEX(@Delimiter,@List,1)      
        if @Pos>0
           begin
             Insert into @TABLEOFVALUES ([Value]) Values (SubString(@List,1,@Pos -1))
             Select @LIST = STUFF(@List,1,@Pos ,'')
           end
        else  
            begin
            Insert into @TABLEOFVALUES ([Value]) Values (@List)
            Select @LIST =''
            end  
      end
   Return 
   End

With call by

Select Distinct b.ArticleID,b.Title,b.Description,b.Details,b.KeyWords from
(
Select * from Article a1 
CROSS APPLY [dbo].[uf_SplitKeywords](',',keywords) f1
Where a1.ArticleID=3
) a
Join
(
Select * from Article a2 
CROSS APPLY [dbo].[uf_SplitKeywords](',',keywords) f2
) b
on a.Value=b.Value

Answer 2

If I understand correctly, you have an article with a "keyword" and "tags". You want to show related articles with same "keyword" or "tags".

Conceptually you need to modify the design of your database tables. Instead of inserting Keywords as a varchar(100) in the Article table, you can create a new table Article_Keywords with ArticleID int as Foreign Key constraint and another column Keywords as varchar.

Then you can join the tables based on article ID. So Article_Keywords would look like:

Best Practice Solution

Dont allow Nulls on ID Columns

alter TABLE Article
alter column ArticleID int not null

Add Primary Key to ID columns for Indexing and Joining with other tables

alter TABLE Article
add constraint PK_ArticleID PRIMARY KEY (ArticleID)

Create New table Article Keywords

create table Article_Keywords
(
    [ArticleID] int not null,
    [Keywords] nvarchar(100),
    Foreign key ([ArticleID]) References Article(ArticleID),
)

INSERT Data

insert into [Article_Keywords] Values (1,'one'),(1,'two'),(1,'three');
insert into [Article_Keywords] Values (2,'two'),(2,'three'),(2,'four');
insert into [Article_Keywords] Values (3,'three'),(3,'four'),(3,'five');
insert into [Article_Keywords] Values (4,'four'),(4,'five'),(4,'six'),(5,'two'),(5,'three'),(6,'eight'),(6,'nine'),(7,'nine'),(7,'ten'),(8,'eleven');

delete from Article_Keywords where [ArticleID] = [Keywords]

SELECT
    DISTINCT AK1.ArticleID
FROM Article_Keywords AK1
WHERE EXISTS (
SELECT AK2.Keywords from Article_Keywords AK2
WHERE AK2.[ArticleID] = 3 AND AK1.[KEYWORDS] = AK2.[KEYWORDS]
) 

However, if you feel the need to go down the way you are going, you could use a cursor (unadvisable):

Create a temporary table to store all Related articles

IF ( OBJECT_ID('tempdb.dbo.#RelatedArticles') IS NOT NULL ) DROP TABLE #RelatedArticles
CREATE TABLE #RelatedArticles (
    ArticleID int
);


DECLARE @VALUE NVARCHAR(100)
DECLARE Keyword_Cursor Cursor For

Use the [uf_SplitKeywords] to extract the keywords from the Article table

SELECT [Value] FROM [uf_SplitKeywords] (',',(SELECT [KEYWORDS] FROM ARTICLE WHERE ARTICLEID = 3))

OPEN Keyword_Cursor

FETCH NEXT FROM Keyword_Cursor into @VALUE

WHILE @@FETCH_STATUS = 0
BEGIN

    insert into #RelatedArticles
    SELECT [ArticleID] from Article
    where [keywords] like (SELECT '%' + @VALUE +  '%');

    FETCH NEXT FROM Keyword_Cursor into @VALUE
END

CLOSE Keyword_Cursor
DEALLOCATE Keyword_Cursor

SELECT DISTINCT ArticleID from #RelatedArticles

Compare the two methods and you will find that having a better database design will save you a lot more time when designing for complex projects.