public LoggedSearchBE SearchAnalytics_GetTrackedQuery(ulong queryId) {
LoggedSearchBE query = null;
Catalog.NewQuery(@"/* SearchAnalytics_GetTrackedQuery */
SELECT * FROM query_log ql WHERE ql.query_id = ?QUERYID;
SELECT qt.query_term
FROM query_term_map qtm
INNER JOIN query_terms qt ON qtm.query_term_id = qt.query_term_id
WHERE qtm.query_id = ?QUERYID;")
.With("QUERYID", queryId)
.Execute(r => {
while(r.Read()) {
query = SearchAnalytics_PopulateTrackQueryBE(r);
}
r.NextResult();
while(r.Read()) {
query.Terms.Add(r.Read<string>("query_term"));
}
});
if(query != null) {
query.SelectedResults = new List<LoggedSearchResultBE>();
Catalog.NewQuery(@"/* SearchAnalytics_GetTrackedQuery */
SELECT * FROM query_result_log qrl WHERE qrl.query_id = ?QUERYID;")
.With("QUERYID", query.Id)
.Execute(r => {
while(r.Read()) {
query.SelectedResults.Add(SearchAnalytics_PopulateSearchResultBE(r));
}
});
}
return query;
}
private void BuildQueryXml(XDoc queryDoc, LoggedSearchBE query)
{
queryDoc
.Attr("id", query.Id)
.Attr("href", _apiUri.At("site", "query", "log", query.Id.ToString()).AsPublicUri());
if (query.PreviousId.HasValue)
{
queryDoc.Start("previous")
.Attr("id", query.PreviousId.Value)
.Attr("href", _apiUri.At("site", "query", "log", query.Id.ToString()).AsPublicUri())
.End();
}
queryDoc
.Elem("date.searched", query.Created)
.Elem("sorted-terms", query.SortedTerms)
.Elem("raw", query.RawQuery)
.Elem("processed", query.ParsedQuery)
.Elem("count.results", query.ResultCount);
// terms block
queryDoc.Start("terms");
foreach (var term in query.Terms)
{
queryDoc.Elem("term", term);
}
queryDoc.End();
// selected result block
queryDoc.Start("selected-results").Attr("count", query.SelectedResults.Count);
foreach (var pick in query.SelectedResults)
{
queryDoc.Start("result")
.Elem("position", pick.Position)
.Elem("rank", pick.Rank);
BuildResultXmlFragment(queryDoc, pick);
queryDoc.End();
}
queryDoc.End();
}
public IEnumerable<LoggedSearchBE> SearchAnalytics_GetTrackedQueries(string querystring, SearchAnalyticsQueryType type, DateTime since, DateTime before, uint? limit, uint? offset) {
var whereClause = new StringBuilder();
SearchAnalytics_BuildDateRangeClause(whereClause);
string joinClause = null;
switch(type) {
case SearchAnalyticsQueryType.Term:
if(string.IsNullOrEmpty(querystring)) {
return new LoggedSearchBE[0];
}
joinClause = @"INNER JOIN query_term_map qtm ON ql.query_id = qtm.query_id
INNER JOIN query_terms qt ON qtm.query_term_id = qt.query_term_id";
whereClause.Append(" AND qt.query_term = ?KEY");
break;
case SearchAnalyticsQueryType.QueryString:
if(querystring == null) {
return new LoggedSearchBE[0];
}
whereClause.Append(" AND ql.sorted_terms = ?KEY");
break;
}
whereClause.Append(" ORDER BY ql.created DESC");
SearchAnalytics_BuildLimitOffsetClause(whereClause, limit, offset);
var queries = new Dictionary<ulong, LoggedSearchBE>();
Catalog.NewQuery(string.Format(@"/* SearchAnalytics_GetTrackedQueries */
SELECT * FROM query_log ql
{0}
{1}", joinClause, whereClause))
.With("SINCE", since)
.With("BEFORE", before)
.With("LIMIT", limit)
.With("OFFSET", offset)
.With("KEY", querystring)
.Execute(r => {
while(r.Read()) {
var entity = SearchAnalytics_PopulateTrackQueryBE(r);
queries.Add(entity.Id, entity);
}
});
if(queries.Any()) {
var idClause = queries.Keys.ToCommaDelimitedString();
// populate terms && results
Catalog.NewQuery(string.Format(@"/* SearchAnalytics_GetTrackedQueries */
SELECT qtm.query_id, qt.query_term
FROM query_term_map qtm
INNER JOIN query_terms qt ON qtm.query_term_id = qt.query_term_id
WHERE qtm.query_id IN ({0});
SELECT *
FROM query_result_log qrl
WHERE qrl.query_id IN ({0});", idClause))
.Execute(r => {
while(r.Read()) {
var id = r.Read<ulong>("query_id");
LoggedSearchBE entity = queries[id];
entity.Terms.Add(r.Read<string>("query_term"));
}
r.NextResult();
while(r.Read()) {
var id = r.Read<ulong>("query_id");
LoggedSearchBE entity = queries[id];
entity.SelectedResults.Add(SearchAnalytics_PopulateSearchResultBE(r));
}
});
}
return queries.Values;
}
