This is the fifteenth part of the SQLxD series. For your convenience you can find other parts in the table of contents in Part 1 – XML Transformation

Last time we implemented grouping, today we focus on ordering. These two operations are very similar.

First, we need to know which column we use for sorting and the order (ascending/descending):

using Model;

namespace QueryLogic.Ordering
{
    public class ColumnOrdering
    {
        public ColumnOrdering(ColumnHeader column, OrderDirection direction = OrderDirection.Ascending)
        {
            Column = column;
            Direction = direction;
        }

        public ColumnHeader Column { get; private set; }
        public OrderDirection Direction { get; private set; }

        protected bool Equals(ColumnOrdering other)
        {
            return Equals(Column, other.Column) && Direction == other.Direction;
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((ColumnOrdering)obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                return ((Column != null ? Column.GetHashCode() : 0) * 397) ^ (int)Direction;
            }
        }
    }
}
namespace QueryLogic.Ordering
{
    public enum OrderDirection
    {
        Ascending,
        Descending
    }
}

And now comes the operator:

using System;
using System.Collections.Generic;
using System.Linq;
using Model;

namespace QueryLogic.Ordering
{
    public class OrderBy
    {
        public OrderBy(IEnumerable columnOrderings, int? skipCount = null,
            int? fetchCount = null)
        {
            ColumnOrderings = columnOrderings;
            SkipCount = skipCount;
            FetchCount = fetchCount;
        }

        public IEnumerable ColumnOrderings { get; private set; }
        public int? FetchCount { get; private set; }
        public int? SkipCount { get; private set; }

        protected bool Equals(OrderBy other)
        {
            return Equals(ColumnOrderings, other.ColumnOrderings) && FetchCount == other.FetchCount &&
                   SkipCount == other.SkipCount;
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((OrderBy)obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                int hashCode = (ColumnOrderings != null ? ColumnOrderings.GetHashCode() : 0);
                hashCode = (hashCode * 397) ^ FetchCount.GetHashCode();
                hashCode = (hashCode * 397) ^ SkipCount.GetHashCode();
                return hashCode;
            }
        }

        public Relation OrderRelation(Relation relation)
        {
            IEnumerable> rowListLists = new List>
            {
                relation.Rows
            };

            foreach (ColumnOrdering columnOrdering in ColumnOrderings)
            {
                ColumnOrdering ordering = columnOrdering;
                rowListLists =
                    rowListLists.SelectMany(rowList => GroupByRowList(rowList, ordering));
            }

            IEnumerable resultRows = rowListLists.SelectMany(x => x).Skip(SkipCount ?? 0);
            return new Relation(FetchCount != null ? resultRows.Take(FetchCount.Value) : resultRows);
        }

        private static IEnumerable> GroupByRowList(IEnumerable rowList,
            ColumnOrdering columnOrdering)
        {
            return OrderRowList(rowList, columnOrdering).GroupBy(GetCellValue(columnOrdering));
        }

        private static IEnumerable OrderRowList(IEnumerable rowList, ColumnOrdering columnOrdering)
        {
            return columnOrdering.Direction == OrderDirection.Ascending
                ? rowList.OrderBy(GetCellValue(columnOrdering))
                : rowList.OrderByDescending(GetCellValue(columnOrdering));
        }

        private static Func GetCellValue(ColumnOrdering columnOrdering)
        {
            return row => row.GetCellValue(columnOrdering.Column);
        }
    }
}

We first group rows and then sort them. And here are the tests:

using Model;
using NUnit.Framework;
using QueryLogic.Ordering;

namespace QueryLogic.Test.Ordering
{
    [TestFixture]
    public class OrderByTests
    {
        [Test]
        public void CreateRelation_DescendingColumnPassed_ShouldOrderWithOneColumnDescending()
        {
            // Arrange
            var firstRow = new Row(new[]
            {
                new Cell("schema", "A", "value3"),
                new Cell("schema", "B", "value1")
            });
            var secondRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value3")
            });
            var thirdRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value2")
            });
            var source = new Relation(new[]
            {
                firstRow,
                secondRow,
                thirdRow
            });

            var orderBy = new OrderBy(new[]
            {
                new ColumnOrdering(new ColumnHeader("schema", "A")),
                new ColumnOrdering(new ColumnHeader("schema", "B"), OrderDirection.Descending)
            });

            var expected = new Relation(new[] { secondRow, thirdRow, firstRow });

            // Act
            Relation actual = orderBy.OrderRelation(source);

            // Assert
            Assert.That(actual, Is.EqualTo(expected));
        }

        [Test]
        public void CreateRelation_FetchCountPassed_ShouldFetchRows()
        {
            // Arrange
            var firstRow = new Row(new[]
            {
                new Cell("schema", "A", "value3"),
                new Cell("schema", "B", "value1")
            });
            var secondRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value2")
            });
            var thirdRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value3")
            });
            var source = new Relation(new[]
            {
                firstRow,
                secondRow,
                thirdRow
            });

            var orderBy = new OrderBy(new[] { new ColumnOrdering(new ColumnHeader("schema", "A")) }, fetchCount: 1);

            var expected = new Relation(new[] { secondRow });

            // Act
            Relation actual = orderBy.OrderRelation(source);

            // Assert
            Assert.That(actual, Is.EqualTo(expected));
        }

        [Test]
        public void CreateRelation_ManyColumnsPassed_ShouldOrderByManyColumns()
        {
            // Arrange
            var firstRow = new Row(new[]
            {
                new Cell("schema", "A", "value3"),
                new Cell("schema", "B", "value1")
            });
            var secondRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value3")
            });
            var thirdRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value2")
            });
            var source = new Relation(new[]
            {
                firstRow,
                secondRow,
                thirdRow
            });

            var orderBy = new OrderBy(new[]
            {
                new ColumnOrdering(new ColumnHeader("schema", "A")),
                new ColumnOrdering(new ColumnHeader("schema", "B"))
            });

            var expected = new Relation(new[] { thirdRow, secondRow, firstRow });

            // Act
            Relation actual = orderBy.OrderRelation(source);

            // Assert
            Assert.That(actual, Is.EqualTo(expected));
        }

        [Test]
        public void CreateRelation_OneColumnPassed_ShouldOrderByOneColumn()
        {
            // Arrange
            var firstRow = new Row(new[]
            {
                new Cell("schema", "A", "value3"),
                new Cell("schema", "B", "value1")
            });
            var secondRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value2")
            });
            var thirdRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value3")
            });
            var source = new Relation(new[]
            {
                firstRow,
                secondRow,
                thirdRow
            });

            var orderBy = new OrderBy(new[] { new ColumnOrdering(new ColumnHeader("schema", "A")) });

            var expected = new Relation(new[] { secondRow, thirdRow, firstRow });

            // Act
            Relation actual = orderBy.OrderRelation(source);

            // Assert
            Assert.That(actual, Is.EqualTo(expected));
        }

        [Test]
        public void CreateRelation_SkipCountPassed_ShouldSkipRows()
        {
            // Arrange
            var firstRow = new Row(new[]
            {
                new Cell("schema", "A", "value3"),
                new Cell("schema", "B", "value1")
            });
            var secondRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value2")
            });
            var thirdRow = new Row(new[]
            {
                new Cell("schema", "A", "value2"),
                new Cell("schema", "B", "value3")
            });
            var source = new Relation(new[]
            {
                firstRow,
                secondRow,
                thirdRow
            });

            var orderBy = new OrderBy(new[] { new ColumnOrdering(new ColumnHeader("schema", "A")) }, 1);

            var expected = new Relation(new[] { thirdRow, firstRow });

            // Act
            Relation actual = orderBy.OrderRelation(source);

            // Assert
            Assert.That(actual, Is.EqualTo(expected));
        }
    }
}