static FacetMoniker[] GetMostUsedFacets(TypeMoniker commandParameterType, CommandParameter parameter, int maxResults)
{
var rvl = new List <FacetMoniker>();
commandParameterType = parameter == null ? commandParameterType : parameter.Type;
var facetTypeIds = Loader.GetFacetTypesImplementing(commandParameterType)
.Select(x => x.DatabaseId.ToString())
.JoinStrings(",");
if (facetTypeIds == "")
{
// no facet types support this command parameter type
return(rvl.ToArray());
}
using (var connection = DatabaseUtil.GetConnection())
{
const string innerSelect =
@"SELECT cua.FacetMonikerId, COUNT(*) AS _count
FROM CommandUsageArguments AS cua
INNER JOIN FacetMonikers AS fm ON fm.Id = cua.FacetMonikerId
{0}
WHERE fm.FacetTypeId IN ({1})
GROUP BY cua.FacetMonikerId";
const string forCommandFragment =
@"INNER JOIN CommandUsages AS cu ON cu.Id = cua.CommandUsageId AND cu.CommandId = @CommandId";
var innerX = String.Format("INNER JOIN ({0}) AS x ON x.FacetMonikerId = fm.Id", String.Format(innerSelect, "", facetTypeIds));
var innerY = parameter == null
? null
: String.Format("LEFT OUTER JOIN ({0}) AS y ON y.FacetMonikerId = fm.Id", String.Format(innerSelect, forCommandFragment, facetTypeIds));
var selectYCount = parameter == null ? "" : ", y._count";
var order = parameter == null ? "x._count DESC" : "COALESCE(y._count, 0) DESC, x._count DESC";
const string cmdText =
@"SELECT fm.FactoryTypeId, fm.FacetTypeId, fm.DisplayName, fm.DateTime, fm.FactoryData, fm.Source, fm.ExtraData, x._count {0}
FROM FacetMonikers AS fm
{1}
{2}
WHERE fm.Id = x.FacetMonikerId
ORDER BY {3}";
var finalCmdText = String.Format(cmdText, selectYCount, innerX, innerY, order);
using (var cmd = new SqlCeCommand(finalCmdText, connection))
{
if (parameter != null)
{
cmd.Parameters.AddWithValue("@CommandId", Loader.GetCommandInfo(parameter.Command).DatabaseId);
}
foreach (var row in cmd.ExecuteReader().AsEnumerable())
{
var moniker = FacetIndex.MaterializeMoniker(row);
if (moniker == null || (parameter != null && !parameter.IsUsableAsArgument(moniker)))
{
continue;
}
rvl.Add(moniker);
if (rvl.Count == maxResults)
{
break;
}
}
}
}
return(rvl.ToArray());
}