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; }