/// <summary>
/// Returns the information on each word with ids in resultIDs.
/// </summary>
/// <param name="explore"></param>
/// <param name="resultIDs"></param>
/// <returns></returns>
private static List <WordwDefinition> GetWordInformation(ExploreVM explore, IEnumerable <int> resultIDs)
{
Sql definitionsSql = new Sql(@"SELECT Words.ID, Words.Word, Words.Popularity, DefinitionID, Definition, Definitions.LanguageID AS LanguageID, NULL As Relationship, NULL AS BaseWord, Rank FROM Definitions
INNER JOIN Word_Definition ON Definitions.ID = Word_Definition.DefinitionID
INNER JOIN Words ON Word_Definition.WordID = Words.ID
WHERE Words.ID IN (@0)", resultIDs);
Sql allRelatedSql = new Sql(@"SELECT Words.ID, Words.Word, Words.Popularity, NULL AS DefinitionID, NULL AS Definition, NULL AS LanguageID, Relationship, BaseWords.Word AS BaseWord, RelationshipID + 20 AS Rank
FROM WordRelationships
INNER JOIN Words ON WordRelationships.WordB = Words.ID
INNER JOIN Relationships ON WordRelationships.RelationshipID = Relationships.ID
INNER JOIN Words AS BaseWords ON WordRelationships.WordA = BaseWords.ID
WHERE Words.ID IN (@0)", resultIDs);
Sql compactInfoSql = new Sql(@"SELECT Words.ID, Words.Word, Words.Popularity, NULL AS DefinitionID, NULL AS Definition, NULL AS LanguageID, Relationship, BaseWords.Word AS BaseWord, RelationshipID + 20 AS Rank
FROM WordRelationships
INNER JOIN Words ON WordRelationships.WordA = Words.ID
INNER JOIN Relationships ON WordRelationships.RelationshipID = Relationships.ID
INNER JOIN Words AS BaseWords ON WordRelationships.WordB = BaseWords.ID
WHERE Words.ID IN (@0)", resultIDs);
//Sql compactInfoSql = new Sql(@"SELECT Words.ID, Words.Word, Words.Popularity, NULL AS DefinitionID, NULL AS Definition, NULL AS LanguageID, Relationship, BaseWords.Word AS BaseWord, RelationshipID + 20 AS Rank
// FROM WordRelationships
// INNER JOIN Words ON WordRelationships.WordB = Words.ID
// INNER JOIN Relationships ON WordRelationships.RelationshipID = Relationships.ID
// INNER JOIN Words AS BaseWords ON WordRelationships.WordA = BaseWords.ID
// WHERE Words.ID IN (@0)", resultIDs);
Sql infoSql;
// if there is only one result, return all of the information available: itself and related entries.
if (resultIDs.ToArray().Length == 1)
{
infoSql = definitionsSql.Append("UNION").Append(allRelatedSql);
}
// otherwise just return where it, itself, has an entry.
else
{
infoSql = definitionsSql.Append("UNION").Append(compactInfoSql);
}
Sql orderBy = new Sql("ORDER BY LanguageID, Rank, DefinitionID");
infoSql.Append(orderBy);
List <WordwDefinition> results = db.Fetch <WordwDefinition>(infoSql);
if (results.Count == 0)
{
results = db.Fetch <WordwDefinition>(compactInfoSql.Append(orderBy));
}
return(results);
}
private static List <string> GetConcepts(ExploreVM explore)
{
PropertyInfo[] properties = typeof(ExploreVM).GetProperties();
List <string> concepts = new List <string>();
foreach (PropertyInfo property in properties)
{
bool isPropBool = property.PropertyType == Type.GetType("System.Boolean");
if (!isPropBool)
{
continue;
}
if (property.Name.StartsWith("is"))
{
continue;
}
bool isChecked = (bool)property.GetValue(explore);
if (isChecked)
{
concepts.Add(property.Name);
}
}
for (int i = 0; i < concepts.Count; i++)
{
string concept = concepts[i];
if (!concept.Contains("_"))
{
continue;
}
string[] elems = concept.Split('_');
concept = elems[1] + "-" + elems[0];
if (elems.Length > 2)
{
concept += "-long";
}
concepts[i] = concept;
}
return(concepts);
}
public static List <FullWord> GetWordsByExplore(ExploreVM explore)
{
IEnumerable <int> resultIDs = GetWordsToReturn(explore);
List <WordwDefinition> resultWords = GetWordInformation(explore, resultIDs);
Dictionary <string, FullWord> resultDict = new Dictionary <string, FullWord>();
foreach (WordwDefinition entry in resultWords)
{
FullWord fw;
// "Search Distance" is a measure of the difference between the search term and the returned term.
// However, if the search is of definitions, the "Search Distance" is instead the
// index of the first appearance of the search term in a definition.
if (resultDict.ContainsKey(entry._Word))
{
fw = resultDict[entry._Word];
}
else
{
fw = new FullWord();
fw._Word = entry._Word;
fw.ID = entry.ID;
fw.Definitions = new List <Definition>();
fw.Popularity = entry.Popularity;
fw.Related = new List <RelatedWord>();
if (explore.isDefinitionSearch)
{
fw.SearchDistance = int.MaxValue; // this is measured later.
}
else
{
fw.SearchDistance = LevenshteinDistance.Compute(fw._Word, explore.search);
}
resultDict[entry._Word] = fw;
}
var isDef = entry.Definition != null;
if (isDef)
{
Definition def = new Definition()
{
_Definition = entry.Definition, LanguageID = entry.LanguageID
};
fw.Definitions.Add(def);
if (explore.isDefinitionSearch)
{
// compute search distance
int defSearchDistance = -1;
if (!String.IsNullOrEmpty(explore.search))
{
def._Definition.IndexOf(explore.search);
}
if (defSearchDistance == -1)
{
defSearchDistance = int.MaxValue;
}
fw.SearchDistance = Math.Min(fw.SearchDistance, defSearchDistance);
}
}
else
{
RelatedWord rel = new RelatedWord()
{
ID = entry.BaseWordID ?? default(int), Relationship = entry.Relationship, Word = entry.BaseWord
};
fw.Related.Add(rel);
}
}
if (resultDict.Count > 0)
{
return(resultDict.Values.OrderBy(e => e.SearchDistance).ToList <FullWord>());
}
else
{
return(new List <FullWord>()); // empty list.
}
}
public static List <FullWord> GetWordsByExploreOLD(ExploreVM explore)
{
PropertyInfo[] properties = typeof(ExploreVM).GetProperties();
List <string> concepts = new List <string>();
foreach (PropertyInfo property in properties)
{
bool isPropBool = property.PropertyType == Type.GetType("System.Boolean");
if (!isPropBool)
{
continue;
}
if (property.Name.StartsWith("is"))
{
continue;
}
bool isChecked = (bool)property.GetValue(explore);
if (isChecked)
{
concepts.Add(property.Name);
}
}
for (int i = 0; i < concepts.Count; i++)
{
string concept = concepts[i];
if (!concept.Contains("_"))
{
continue;
}
string[] elems = concept.Split('_');
concept = elems[1] + "-" + elems[0];
if (elems.Length > 2)
{
concept += "-long";
}
concepts[i] = concept;
}
bool hasSearchTerm = !string.IsNullOrWhiteSpace(explore.search);
Sql sql = new Sql(@"SELECT Words.ID, Words.Word, Words.Popularity, Definition");
if (explore.isDefinitionSearch)
{
sql.Append(@"FROM Definitions
INNER JOIN Word_Definition ON Definitions.ID = Word_Definition.DefinitionID
INNER JOIN Words ON Word_Definition.WordID = Words.ID");
}
else
{
sql.Append(@", Relationship, BaseWords.Word as BaseWord FROM Words
LEFT OUTER JOIN Word_Definition ON Words.ID = Word_Definition.WordID
LEFT OUTER JOIN Definitions ON Word_Definition.DefinitionID = Definitions.ID
LEFT OUTER JOIN WordRelationships ON Words.ID = WordRelationships.WordA
LEFT OUTER JOIN Relationships ON WordRelationships.RelationshipID = Relationships.ID
LEFT OUTER JOIN Words AS BaseWords ON WordRelationships.WordB = BaseWords.ID");
}
if (hasSearchTerm)
{
if (explore.originalSearch == null)
{
explore.originalSearch = explore.search.Replace("*", "");
}
string searchTerm = explore.search.Replace('*', '%');
if (explore.isDefinitionSearch)
{
if (!searchTerm.Contains('%'))
{
searchTerm = string.Format("%{0}%", searchTerm);
}
sql.Append(@"WHERE Definition LIKE (@0)", searchTerm);
}
else
{
searchTerm = searchTerm.Replace("a:", "ä").Replace("o:", "ö"); // perhaps this should be on the client side
searchTerm = searchTerm.Trim().ToLower();
sql.Append(@"WHERE Words.Word LIKE (@0)", searchTerm);
}
}
// sql.Append(@"");
if (concepts.Count > 0)
{
if (hasSearchTerm)
{
sql.Append("AND");
}
else
{
sql.Append("WHERE");
}
sql.Append(@"Words.ID IN (
SELECT Words.ID FROM Words
INNER JOIN Word_Concept ON Words.ID = Word_Concept.WordID
INNER JOIN Concepts ON Word_Concept.ConceptID = Concepts.ID
WHERE Concept IN (@0) AND Popularity IS NOT NULL
GROUP BY Words.ID
HAVING COUNT(*) = @1)", concepts.ToArray(), concepts.Count);
}
if (explore.isDefinitionSearch)
{
sql.Append("ORDER BY Words.Popularity DESC, Words.Word");
}
else
{
sql.Append("ORDER BY Words.Popularity DESC, Words.ID, RelationshipID");
}
Debug.WriteLine(sql);
List <WordwDefinition> wordsDefs = db.Fetch <WordwDefinition>(sql);
List <FullWord> resultSet = new List <FullWord>();
List <string> relList = new List <string>();
// TODO: There is a way to do this with LINQ but, for now....
int wordID = 0;
FullWord word = null;
WordwDefinition wDef = null;
for (int i = 0; i < wordsDefs.Count; i++)
{
wDef = wordsDefs[i];
bool isNew = wordID != wDef.ID;
if (isNew)
{
if (word != null && relList.Count > 0)
{ // finish up the previous word
// adding definitions derived from relationships at the end of the list of definitions.
if (word.Definitions == null)
{
word.Definitions = new List <Definition>();
}
for (int j = 0; j < relList.Count; j++)
{
Definition def = new Definition()
{
_Definition = relList[j]
};
word.Definitions.Add(def);
}
}
word = new FullWord();
relList = new List <string>();
word.ID = wDef.ID;
word._Word = wDef._Word;
word.Popularity = wDef.Popularity;
resultSet.Add(word);
wordID = wDef.ID;
word.SearchDistance = LevenshteinDistance.Compute(word._Word, explore.originalSearch);
}
if (!string.IsNullOrEmpty(wDef.Definition))
{
if (word.Definitions == null)
{
word.Definitions = new List <Definition>();
}
bool hasDef = word.Definitions.Any <Definition>(t => t._Definition == wDef.Definition);
if (!hasDef)
{
Definition def = new Definition()
{
_Definition = wDef.Definition
};
if (!word.Definitions.Contains(def))
{
word.Definitions.Add(def);
}
}
}
if (!string.IsNullOrEmpty(wDef.Relationship))
{
string relDefStr = String.Format("{0}: <a href={1}>{1}</a>", wDef.Relationship, wDef.BaseWord);
if (!relList.Contains(relDefStr))
{
relList.Add(relDefStr);
}
}
}
if (resultSet.Count == 1)
{
Sql relatedSql = new Sql(@"SELECT RelatedWords.Word, Relationship FROM WordRelationships
INNER JOIN Words AS RelatedWords ON WordRelationships.WordA = RelatedWords.ID
INNER JOIN Words ON WordRelationships.WordB = Words.ID
INNER JOIN Relationships ON WordRelationships.RelationshipID = Relationships.ID
WHERE Words.Word = @0
ORDER BY RelationshipID", explore.search);
List <WordwDefinition> relWordList = db.Fetch <WordwDefinition>(relatedSql);
if (relWordList.Count > 0)
{
relList = new List <string>();
}
for (int k = 0; k < relWordList.Count; k++)
{
WordwDefinition relW = relWordList[k];
if (!string.IsNullOrEmpty(relW.Relationship))
{
string relDefStr = String.Format("{0}: <a href={1}>{1}</a>", relW.Relationship, relW._Word);
if (!relList.Contains(relDefStr))
{
relList.Add(relDefStr);
}
}
}
}
if (word != null && wDef != null && relList.Count > 0)
{ // finish up the previous word
// adding definitions derived from relationships at the end of the list of definitions.
if (word.Definitions == null)
{
word.Definitions = new List <Definition>();
}
for (int j = 0; j < relList.Count; j++)
{
Definition def = new Definition()
{
_Definition = relList[j]
};
word.Definitions.Add(def);
}
}
if (resultSet.Count == 0 && explore.search.Length > 1)
{
string broaderTerm = explore.search;
bool hasEndWildcard = (broaderTerm[broaderTerm.Length - 1] == '*');
if (hasEndWildcard)
{
broaderTerm = broaderTerm.Substring(0, broaderTerm.Length - 2);
}
broaderTerm += '*';
explore.search = broaderTerm;
resultSet = GetWordsByExplore(explore);
}
if (!explore.isDefinitionSearch && hasSearchTerm && resultSet.Count > 0 && resultSet.First().SearchDistance > 0)
{
resultSet.Sort((a, b) => a.SearchDistance.CompareTo(b.SearchDistance));
}
return(resultSet);
}
/// <summary>
/// Returns the ids of the words wanted.
/// </summary>
/// <param name="explore"></param>
/// <returns></returns>
private static IEnumerable <int> GetWordsToReturn(ExploreVM explore)
{ // This just returns a set of ids.
IEnumerable <int> resIDs;
string searchTerm = explore.search;
bool hasSearchTerm = !string.IsNullOrEmpty(searchTerm);
if (explore.isAdmin)
{
explore.rowsPerPage = 500;
}
List <string> concepts = GetConcepts(explore);
do
{
Sql sql = new Sql();
sql.Append(@"SELECT Words.ID FROM Words");
if (hasSearchTerm)
{
string sqlSearchTerm = searchTerm.Replace('*', '%');
sqlSearchTerm = sqlSearchTerm.Trim().ToLower();
if (explore.isDefinitionSearch)
{
if (!sqlSearchTerm.Contains('%'))
{
sqlSearchTerm = string.Format("%{0}%", sqlSearchTerm);
}
sql.Append(@"INNER JOIN Word_Definition ON Words.ID = Word_Definition.WordID
INNER JOIN Definitions ON Word_Definition.DefinitionID = Definitions.ID
WHERE Definition LIKE (@0)", sqlSearchTerm);
}
else
{
sql.Append(@"WHERE Words.Word LIKE (@0)", sqlSearchTerm);
}
}
if (concepts.Count > 0)
{
if (hasSearchTerm)
{
sql.Append("AND");
}
else
{
sql.Append("WHERE");
}
sql.Append(@"Words.ID IN (
SELECT Words.ID FROM Words
INNER JOIN Word_Concept ON Words.ID = Word_Concept.WordID
INNER JOIN Concepts ON Word_Concept.ConceptID = Concepts.ID
WHERE Concept IN (@0) AND Popularity IS NOT NULL
GROUP BY Words.ID
HAVING COUNT(*) = @1)", concepts.ToArray(), concepts.Count);
}
sql.Append("ORDER BY Popularity DESC");
int offset = explore.pageNum * explore.rowsPerPage;
int fetchNext = explore.rowsPerPage;
sql.Append("OFFSET @0 ROWS", offset);
sql.Append("FETCH NEXT @0 ROWS ONLY", fetchNext);
resIDs = db.Fetch <int>(sql);
if (resIDs.Any() || string.IsNullOrEmpty(searchTerm))
{
continue;
}
bool hasEndWildcard = (searchTerm[searchTerm.Length - 1] == '*');
if (hasEndWildcard)
{
searchTerm = searchTerm.Substring(0, searchTerm.Length - 2);
}
searchTerm += '*';
} while (!resIDs.Any() && searchTerm.Length > 2);
return(resIDs);
}
