Matching several keywords against articles containing them then order by desc
I am trying to figure out how to solve this problem, but can’t wrap my head around it- or to find the best way to do it. What I want to achieve is the following (Kw stands for keyword).
Existing kw in db across all articles:
Kw1, Kw2, Kw3, Kw4, Kw5
Articles using the keywords
Art1: Kw1, Kw2, Kw3
Art2: Kw1, Kw2, Kw4
Art3: Kw4, Kw1, Kw5
Art4: Kw5, Kw4, Kw2
Keywords to match (submitted by user)
Kw1, Kw2, Kw3
Rank based on most number of keyword matches
Art 3 and 4
I am trying to achieve this using a ASP.NET MVC project with Entity Framework. The project is connected to a MS SQL database containing one table with the articles, and another table with the keywords connected with each article.
My initial thoughts was to use LINQ in some way, but I find it hard to figure out just how to do this, if even possible.
All help sending me towards the right path on how to solve this would be very much appreciated!
Edit: Data structure
**KeywordTbl** [KnowledgeDatabaseKeywordId] INT IDENTITY (1, 1) NOT NULL, [Keyword] NVARCHAR (64) NOT NULL, [KnowledgeDatabaseId] INT NOT NULL, **ArticlesTbl** [KnowledgeDatabaseId] INT IDENTITY (1, 1) NOT NULL, [Title] NVARCHAR (200) NOT NULL, [Summary] NVARCHAR (1000) NOT NULL, [Article] NVARCHAR (MAX) NOT NULL,
**ArticlesTbl** KnowledgeDatabaseId = 1 Title = "My title" Summary = "A short summary" Article = "Longer text" **KeywordTbl** KnowledgeDatabaseKeywordId = 1 Keyword = "Kw1" KnowledgeDatabaseId = 1 KnowledgeDatabaseKeywordId = 2 Keyword = "Kw2" KnowledgeDatabaseId = 1
One Solution collect form web for “Matching several keywords against articles containing them then order by desc”
Without knowing what your data structure is this is a rather hard question to answer, but hopefully the below is a good starting point to get your head around how SQL may be able to handle what you want.
If you have your
Keywords held in a table alongside your
Articles, you can create a table of
Keywords to look up, either with temp tables, table variables or derived tables (I have used table variables for ease of writing the example) and then count all the matches that are returned from a
declare @t table (ArticleID int, Keyword nvarchar(10)); insert into @t values (1,'Kw1'),(1,'Kw2'),(1,'Kw3'),(2,'Kw1'),(2,'Kw2'),(2,'Kw4'),(3,'Kw4'),(3,'Kw1'),(3,'Kw5'),(4,'Kw5'),(4,'Kw4'),(4,'Kw2'); declare @kw table (Keyword nvarchar(10)); insert into @kw values('Kw1'),('Kw2'),('Kw3'); select t.ArticleID ,count(k.Keyword) as Matches from @t t left join @kw k on(t.Keyword = k.Keyword) group by t.ArticleID order by Matches desc ,t.ArticleID;
+-----------+---------+ | ArticleID | Matches | +-----------+---------+ | 1 | 3 | | 2 | 2 | | 3 | 1 | | 4 | 1 | +-----------+---------+