List <string> BuildFullTextQueries(FeatureEntityCountQuery <TModel> query)
{
// Parse keywords into valid full text query syntax
var fullTextQuery = _fullTextQueryParser.ToFullTextSearchQuery(query.Params.Keywords.Value);
// Ensure parse was successful
if (!String.IsNullOrEmpty(fullTextQuery))
{
fullTextQuery = fullTextQuery.Replace("'", "''");
}
// Can be empty if only puntutaton or stop words were entered
if (string.IsNullOrEmpty(fullTextQuery))
{
return(null);
}
/*
* Produces the following federated query...
* -----------------
* SELECT el.EntityId, SUM(i.[Rank]) AS [Rank]
* FROM plato_Labels l INNER JOIN
* CONTAINSTABLE(plato_Labels, *, 'FORMSOF(INFLECTIONAL, creative)') AS i ON i.[Key] = l.Id
* INNER JOIN plato_EntityLabels el ON el.LabelId = l.Id
* INNER JOIN plato_Entities e ON e.Id = el.EntityId
* WHERE (l.Id IN (IsNull(i.[Key], 0))) GROUP BY el.EntityId;
*/
var q1 = new StringBuilder();
q1
.Append("SELECT el.EntityId, SUM(i.[Rank]) ")
.Append("FROM ")
.Append("{prefix}_Labels")
.Append(" l ")
.Append("INNER JOIN ")
.Append(query.Options.SearchType.ToString().ToUpper())
.Append("(")
.Append("{prefix}_Labels")
.Append(", *, '").Append(fullTextQuery).Append("'");
if (query.Options.MaxResults > 0)
{
q1.Append(", ").Append(query.Options.MaxResults.ToString());
}
q1.Append(") AS i ON i.[Key] = l.Id ")
.Append("INNER JOIN {prefix}_EntityLabels el ON el.LabelId = l.Id ")
.Append("INNER JOIN plato_Entities e ON e.Id = el.EntityId ")
.Append("WHERE ");
if (!string.IsNullOrEmpty(query.Builder.Where))
{
q1.Append("(").Append(query.Builder.Where).Append(") AND ");
}
q1.Append("(l.Id IN (IsNull(i.[Key], 0))) GROUP BY el.EntityId;");
// Return queries
return(new List <string>()
{
q1.ToString()
});
}
List <string> BuildSqlQueries(FeatureEntityCountQuery <TModel> query)
{
/*
* Produces the following federated query...
* -----------------
* SELECT el.EntityId, 0 FROM plato_Labels l
* INNER JOIN plato_EntityLabels el ON el.LabelId = l.Id
* INNER JOIN plato_Entities e ON e.Id = el.EntityId
* WHERE (l.[Name] LIKE '%percent') GROUP BY el.EntityId;
*/
var q1 = new StringBuilder();
q1.Append("SELECT el.EntityId, 0 FROM {prefix}_Labels l ")
.Append("INNER JOIN {prefix}_EntityLabels el ON el.LabelId = l.Id ")
.Append("INNER JOIN {prefix}_Entities e ON e.Id = el.EntityId ")
.Append("WHERE (");
if (!string.IsNullOrEmpty(query.Builder.Where))
{
q1.Append("(").Append(query.Builder.Where).Append(") AND ");
}
q1.Append("(")
.Append(query.Params.Keywords.ToSqlString("l.[Name]", "Keywords"))
.Append(" OR ")
.Append(query.Params.Keywords.ToSqlString("l.[Description]", "Keywords"))
.Append("));");
// Return queries
return(new List <string>()
{
q1.ToString()
});
}
List <string> BuildSqlQueries(FeatureEntityCountQuery <TModel> query)
{
/*
* Produces the following federated query...
* -----------------
* SELECT ef.EntityId, 0 FROM plato_Files f
* INNER JOIN plato_EntityFiles ea ON ef.FileId = f.Id
* INNER JOIN plato_Entities e ON e.Id = ef.EntityId
* WHERE (a.[Name] LIKE '%percent') GROUP BY ef.EntityId;
*/
var q1 = new StringBuilder();
q1.Append("SELECT ef.EntityId, 0 FROM {prefix}_Files f ")
.Append("INNER JOIN {prefix}_EntityFiles ef ON ef.FileId = f.Id ")
.Append("INNER JOIN {prefix}_Entities e ON e.Id = ef.EntityId ")
.Append("WHERE (");
if (!string.IsNullOrEmpty(query.Builder.Where))
{
q1.Append("(").Append(query.Builder.Where).Append(") AND ");
}
q1.Append("(")
.Append(query.Params.Keywords.ToSqlString("f.[Name]", "Keywords"))
.Append(" OR ")
.Append(query.Params.Keywords.ToSqlString("f.Extension", "Keywords"))
.Append("));");
// Return queries
return(new List <string>()
{
q1.ToString()
});
}
// ----------
IList <string> BuildSqlQueries(FeatureEntityCountQuery <TModel> query)
{
// Entities
// ----------------------
var q1 = new StringBuilder();
q1.Append("SELECT e.Id, 0 AS [Rank] FROM ")
.Append("{prefix}_Entities")
.Append(" e WHERE (");
if (!string.IsNullOrEmpty(query.Builder.Where))
{
q1.Append("(").Append(query.Builder.Where).Append(") AND ");
}
q1.Append("(")
.Append(query.Params.Keywords.ToSqlString("e.Title", "Keywords"))
.Append(" OR ")
.Append(query.Params.Keywords.ToSqlString("e.Message", "Keywords"))
.Append("));");
// Entity Replies
// ----------------------
var q2 = new StringBuilder();
q2.Append("SELECT er.EntityId, 0 AS [Rank] FROM ")
.Append("{prefix}_EntityReplies ")
.Append("er INNER JOIN {prefix}_Entities ")
.Append("e ON e.Id = er.EntityId ")
.Append(" WHERE (");
if (!string.IsNullOrEmpty(query.Builder.Where))
{
q2.Append("(").Append(query.Builder.Where).Append(") AND ");
}
q2.Append("(")
.Append(query.Params.Keywords.ToSqlString("er.Message", "Keywords"))
.Append(")) GROUP BY er.EntityId");
// Return queries
return(new List <string>()
{
q1.ToString(),
q2.ToString()
});
}
IList <string> BuildFullTextQueries(FeatureEntityCountQuery <TModel> query)
{
// Parse keywords into valid full text query syntax
var fullTextQuery = _fullTextQueryParser.ToFullTextSearchQuery(query.Params.Keywords.Value);
// Ensure parse was successful
if (!String.IsNullOrEmpty(fullTextQuery))
{
fullTextQuery = fullTextQuery.Replace("'", "''");
}
// Can be empty if only puntutaton or stop words were entered
if (string.IsNullOrEmpty(fullTextQuery))
{
return(null);
}
var q1 = new StringBuilder();
q1
.Append("SELECT i.[Key], i.[Rank] ")
.Append("FROM ")
.Append("{prefix}_Entities e INNER JOIN ")
.Append(query.Options.SearchType.ToString().ToUpper())
.Append("({prefix}_Entities")
.Append(", *, '").Append(fullTextQuery).Append("'");
if (query.Options.MaxResults > 0)
{
q1.Append(", ").Append(query.Options.MaxResults.ToString());
}
q1.Append(") AS i ON i.[Key] = e.Id WHERE ");
if (!string.IsNullOrEmpty(query.Builder.Where))
{
q1.Append("(").Append(query.Builder.Where).Append(") AND ");
}
q1.Append("(e.Id IN (IsNull(i.[Key], 0)));");
// Entity replies
// ----------------------
var q2 = new StringBuilder();
q2
.Append("SELECT er.EntityId, SUM(i.[Rank]) AS [Rank] ")
.Append("FROM ")
.Append("{prefix}_EntityReplies")
.Append(" er ")
.Append("INNER JOIN ")
.Append(query.Options.SearchType.ToString().ToUpper())
.Append("(")
.Append("{prefix}_EntityReplies")
.Append(", *, '").Append(fullTextQuery).Append("'");
if (query.Options.MaxResults > 0)
{
q2.Append(", ").Append(query.Options.MaxResults.ToString());
}
q2.Append(") i ON i.[Key] = er.Id ")
.Append("INNER JOIN {prefix}_Entities e ON e.Id = er.EntityId ")
.Append("WHERE ");
if (!string.IsNullOrEmpty(query.Builder.Where))
{
q2.Append("(").Append(query.Builder.Where).Append(") AND ");
}
q2.Append("(er.Id IN (IsNull(i.[Key], 0))) ")
.Append("GROUP BY er.EntityId, i.[Rank];");
// Return queries
return(new List <string>()
{
q1.ToString(),
q2.ToString()
});
}
