protected bool AddChildAggregateAttributeSortInformation(ISortInformation sortInformation, Namespace ns, BindVarNamespace bvNamespace, RequestedAttribute miaIdAttribute, IList <TableJoin> tableJoins,
IList <CompiledSortInformation> compiledSortInformation, IList <BindVar> bindVars)
{
ChildAggregateAttributeSortInformation childAttributeSort = sortInformation as ChildAggregateAttributeSortInformation;
if (childAttributeSort != null)
{
if (childAttributeSort.ChildAttributeType.ParentMIAM.IsTransientAspect)
{
return(false);
}
var relation = _miaManagement.LocallyKnownRelationshipTypes.FirstOrDefault(r => (r.ChildRole == childAttributeSort.ChildRole && r.ParentRole == childAttributeSort.ParentRole));
if (relation == null)
{
return(false);
}
if (!_miaManagement.ManagedMediaItemAspectTypes.TryGetValue(relation.ChildAspectId, out var childMetadata))
{
return(false);
}
BindVar roleVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), childAttributeSort.ParentRole, typeof(Guid));
bindVars.Add(roleVar);
BindVar linkedRoleVar = new BindVar(bvNamespace.CreateNewBindVarName("V"), childAttributeSort.ChildRole, typeof(Guid));
bindVars.Add(linkedRoleVar);
var table = new TableQueryData($"({BuildRelationshipPart(childAttributeSort, childMetadata, false, roleVar, linkedRoleVar)} UNION {BuildRelationshipPart(childAttributeSort, childMetadata, true, roleVar, linkedRoleVar)})");
var tableName = table.GetAlias(ns);
tableJoins.Add(new TableJoin("INNER JOIN", table, $"{tableName}.ID", miaIdAttribute.GetQualifiedName(ns)));
RequestedAttribute ra = new RequestedAttribute(table, "SORT");
compiledSortInformation.Add(new CompiledSortInformation(ra, childAttributeSort.Direction));
return(true);
}
return(false);
}
private string BuildRelationshipPart(ChildAggregateAttributeSortInformation childAttributeSort, MediaItemAspectMetadata childMetadata, bool reverse, BindVar roleVar, BindVar linkedRoleVar)
{
string selectColumn;
string roleColumn;
string linkedRoleColumn;
string linkedIdColumn;
if (reverse)
{
//if this is the reverse part, reverse the column names
selectColumn = _miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_LINKED_ID);
roleColumn = _miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_LINKED_ROLE);
linkedRoleColumn = _miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_ROLE);
linkedIdColumn = MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME;
}
else
{
selectColumn = MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME;
roleColumn = _miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_ROLE);
linkedRoleColumn = _miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_LINKED_ROLE);
linkedIdColumn = _miaManagement.GetMIAAttributeColumnName(RelationshipAspect.ATTR_LINKED_ID);
}
StringBuilder joinSql = new StringBuilder();
joinSql.Append($"SELECT R.{selectColumn} ID, ");
if (childAttributeSort.AggregateFunction == AggregateFunction.Avg)
{
joinSql.Append("AVG");
}
else if (childAttributeSort.AggregateFunction == AggregateFunction.Count)
{
joinSql.Append("COUNT");
}
else if (childAttributeSort.AggregateFunction == AggregateFunction.Max)
{
joinSql.Append("MAX");
}
else if (childAttributeSort.AggregateFunction == AggregateFunction.Min)
{
joinSql.Append("MIN");
}
else if (childAttributeSort.AggregateFunction == AggregateFunction.Sum)
{
joinSql.Append("SUM");
}
if (childMetadata.AspectId != childAttributeSort.ChildAttributeType.ParentMIAM.AspectId)
{
joinSql.Append($"(S.{_miaManagement.GetMIAAttributeColumnName(childAttributeSort.ChildAttributeType)}) SORT");
}
else
{
joinSql.Append($"(C.{_miaManagement.GetMIAAttributeColumnName(childAttributeSort.ChildAttributeType)}) SORT");
}
joinSql.Append(" FROM ");
joinSql.Append(_miaManagement.GetMIATableName(childMetadata));
joinSql.Append(" C");
joinSql.Append(" INNER JOIN ");
joinSql.Append(_miaManagement.GetMIATableName(RelationshipAspect.Metadata));
joinSql.Append(" R ON R.");
joinSql.Append(linkedIdColumn);
joinSql.Append("=C.");
joinSql.Append(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
joinSql.Append(" AND R.");
joinSql.Append(linkedRoleColumn);
joinSql.Append("=@" + linkedRoleVar.Name);
joinSql.Append(" AND R.");
joinSql.Append(roleColumn);
joinSql.Append("=@" + roleVar.Name);
//If sort property is from a different aspect we must also join the sort aspect
if (childMetadata.AspectId != childAttributeSort.ChildAttributeType.ParentMIAM.AspectId)
{
joinSql.Append(" LEFT OUTER JOIN ");
joinSql.Append(_miaManagement.GetMIATableName(childAttributeSort.ChildAttributeType.ParentMIAM));
joinSql.Append($" S ON S.");
joinSql.Append(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
joinSql.Append("=C.");
joinSql.Append(MIA_Management.MIA_MEDIA_ITEM_ID_COL_NAME);
}
joinSql.Append($" GROUP BY R.{selectColumn}");
return(joinSql.ToString());
}
