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