Search based on a set of keywords

Question

I need to make a search based on a set of keywords, that return all the Ads related with those keywords. Then the result is a list of Categories with the Ads Count for each Category.

The search is made in a KeywordSearch Table:

public class KeywordSearch
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Keyword Keyword { get; set; }
}

Where the Keyword Table is:

public class Keyword
{
    public int Id { get; set; }
    public string Name { get; set; }
}

The Ads are related with the Keywords using the following Table:

public class KeywordAdCategory
{
    [Key]
    [Column("Keyword_Id", Order = 0)]
    public int Keyword_Id { get; set; }

    [Key]
    [Column("Ad_Id", Order = 1)]
    public int Ad_Id { get; set; }

    [Key]
    [Column("Category_Id", Order = 2)]
    public int Category_Id { get; set; }
}

Finally, the Category table:

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Example:

• Keywords: "Mercedes-Benz" and "GLK"
• KeywordSearch: "Mercedes" and "Benz" for the Keyword "Mercedes-Benz" "GLK" for the Keyword "GLK"
• Category: "Cars" and "Trucks"

• Ads: Car - Mercedes-Benz GLK Truck - Mercedes-Benz Citan

  If I search "Mercedes-Benz" I get:

  • Cars: 1
  • Trucks: 1

  If I search "Mercedes-Benz GLK" I get:

  • Cars: 1

  If I search "Mercedes Citan" I get:

  • Trucks: 1

What I get until now:

var keywordIds = from k in keywordSearchQuery
                    where splitKeywords.Contains(k.Name)
                    select k.Keyword.Id;

var matchingKac = from kac in keywordAdCategoryQuery
                    where keywordIds.Distinct().Contains(kac.Keyword_Id)
                    select kac;

var addIDs = from kac in matchingKac
             group kac by kac.Ad_Id into d
             where d.Count() == splitKeywords.Count()
             select d.Key;

var groupedKac = from kac in keywordAdCategoryQuery
                    where addIDs.Contains(kac.Ad_Id)               <--- EDIT2
                    group kac by new { kac.Category_Id, kac.Ad_Id };

var result = from grp in groupedKac
                group grp by grp.Key.Category_Id into final
                join c in categoryQuery on final.Key equals c.Id
                select new CategoryGetAllBySearchDto
                {
                    Id = final.Key,
                    Name = c.Name,
                    ListController = c.ListController,
                    ListAction = c.ListAction,
                    SearchCount = final.Count()
                };

The problem is that I can't get only the Ads that match all Keywords.

EDIT:

When a keyword is made of 2 or more KeywordSearches like "Mercedes-Benz", the line "where d.Count() == splitKeywords.Count()" fails, because d.count = 1 and splitkeywords.Count = 2 for "Mercedes-Benz"

Any Help?

Answer 1

this may not be the direct answer, but in such "multiple parameter search" situations i just forget about anything and do the simple thing, for ex: Search By Car Manufacturer, CategoryId, MillageMax, Price :

var searchResults = from c in carDb.Cars
where (c.Manufacturer.Contains(Manufacturer) || Manufacturer == null) &&
                 (c.CategoryId == CategoryId || CategoryId == null) &&
                    (c.Millage <= MillageMax || MillageMax== null) &&
                          (c.Price <= Price  || Price == null) 
select c

now if any of the parameters is null it cancels the containing line by making the whole expression in brackets True and so it does not take a part in search any more

Answer 2

If you try to make your own search engine you will probably fail.Why don't you try Lucene. Here's a link http://lucenenet.apache.org/. Cheers

Answer 3

I haven't compile-checked this or anything, so it may require some tweaking, but you're looking for something along these lines.

var matchingKac = keywordIds.Distinct().ToList()
    .Aggregate(
        keywordAdCategoryQuery.AsQueryable(),
        (q, id) => q.Where(kac => kac.Keyword_Id == id));

You're effectively saying, "Start with keywordAdCategoryQuery, and for each keyword add a .Where() condition saying that it must have that keyword in it. You could do the same thing with a for loop if you find Aggregate difficult to read.

Answer 4

I think I have a solution now. This is based on your previous question and a few assumptions:

1. Keywords are complete names like "Mercedes-Benz GLK", "Mercedes-Benz Citan".
2. KeywordSearchs are "Mercedes", "Benz" and "GLK" for "Mercedes-Benz GLK" and "Mercedes", "Benz" and "Citan" for "Mercedes-Benz Citan"
3. "Mercedes-Benz GLK" is a "Car", "Mercedes-Benz Citan" is a "Truck"

With those three assumptions in mind I can say that

var keywordIds = from k in keywordSearchQuery
                 where splitKeywords.Contains(k.Name)
                 select k.Keyword.Id;

is the culprit and all queries below rely on it. This query will find all keywords that contain any words in your searchstring.

Example: Given searchstring "Mercedes-Benz GLK" will be split into "Mercedes", "Benz" and "GLK". Your query now finds "Mercedes" and "Benz" in both "Mercedes-Benz GLK" and "Mercedes-Benz Citan".
I think it's obvious that you don't want "Mercedes-Benz GLK" to match "Mercedes-Benz Citan".

The solution is to tell the query to match every splitKeywords with any Keywordsearch and return the appropriate Keyword:

var keywordIds = keywordSearchQuery
                 .GroupBy(k => k.Keyword.Id)
                 .Where(g => splitKeywords.All(w => 
                                               g.Any(k => k.Name.Contains(w))))
                 .Select(g => g.Key);

As for addIds changing it to var addIDs = matchingKac.Select(ad => ad.Ad_Id).Distinct(); should do the trick. Or if matchingKac is only needed in addIds then you could change it to

var matchingKac = (from kac in keywordAdCategoryQuery
                   where keywordIds.Distinct().Contains(kac.Keyword_Id)
                   select kac.Ad_Id).Distinct();

and remove addIds.

Answer 5

I am suggesting you to add regex and omit that special characters and then use Linq for that

So Mercedez-Benz can become Mercedez and benz

Answer 6

I recommend to NOT define keywords to objects that way, because you might search and find too many objects or you'll find possibly nothing. You will always spoil your time when searching. Classify your objects in a way that the users focus is to FIND and not to search.

Answer 7

I have posted my answer to: https://github.com/n074v41l4bl34u/StackOverflow19796132 Feel free to review it.

Here is the most important snippet.

---

with:

internal class SearchDomain
{
  public List<Keyword> Keywords { get; set; }
  public List<Category> Categories { get; set; }
  public List<KeywordAdCategory> KeywordAdCategories { get; set; }
}

---

then:

private static char[] keywordPartsSplitter = new char[] { ' ', '-' };

internal static Dictionary<Category, Dictionary<int, List<KeywordAdCategory>>> FromStringInput(string searchPhrase, SearchDomain searchDomain)
{
  var identifiedKeywords = searchPhrase
    .Split(keywordPartsSplitter);

  var knownKeywordParts = identifiedKeywords
    .Where
    (ik =>
      searchDomain
      .Keywords
      .SelectMany(x => x.GetKeywordParts())
      .Any(kp => kp.Equals(ik, StringComparison.InvariantCultureIgnoreCase))
    );

  var keywordkSearches = knownKeywordParts
    .Select((kkp, n) => new KeywordSearch()
    {
      Id = n,
      Name = kkp,
      Keyword = searchDomain
        .Keywords
        .Single
        (k =>
          k.GetKeywordParts()
            .Any(kp => kp.Equals(kkp, StringComparison.InvariantCultureIgnoreCase))
        )
    });

  var relevantKeywords = keywordkSearches
    .Select(ks => ks.Keyword)
    .Distinct();

  var keywordAdCategoriesByCategory = searchDomain.Categories
    .GroupJoin
    (
      searchDomain.KeywordAdCategories,
      c => c.Id,
      kac => kac.Category_Id,
      (c, kac) => new { Category = c, AdKeywordsForCategory = kac }
    );

  var relevantKeywordAdCategories = keywordAdCategoriesByCategory
    .Where
    (kacbk =>
      relevantKeywords
        .All
        (rk =>
          kacbk
            .AdKeywordsForCategory
            .Any(kac => kac.Keyword_Id == rk.Id)
        )
    );

  var foundAdsInCategories = relevantKeywordAdCategories
    .ToDictionary
    (rkac =>
      rkac.Category,
      rkac => rkac.AdKeywordsForCategory
        .GroupBy(g => g.Ad_Id)
        .ToDictionary(x => x.Key, x => x.ToList())
    );

  return foundAdsInCategories;
}

---

It does exactly what you want however I find something fishy about keywords being divisible to sub-keywords. Than again, maybe it is just the naming.