SQLxD Part 15 — Ordering – Random IT Utensils

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

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

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<ColumnOrdering> columnOrderings, int? skipCount = null,
            int? fetchCount = null)
        {
            ColumnOrderings = columnOrderings;
            SkipCount = skipCount;
            FetchCount = fetchCount;
        }

        public IEnumerable<ColumnOrdering> 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<IEnumerable<Row>> rowListLists = new List<IEnumerable<Row>>
            {
                relation.Rows
            };

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

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

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

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

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

using System;

using System.Collections.Generic;

using System.Linq;

using Model;

namespace QueryLogic.Ordering

{

public class OrderBy

{

public OrderBy(IEnumerable<ColumnOrdering> columnOrderings, int? skipCount = null,

int? fetchCount = null)

{

ColumnOrderings = columnOrderings;

SkipCount = skipCount;

FetchCount = fetchCount;

}

public IEnumerable<ColumnOrdering> 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<IEnumerable<Row>> rowListLists = new List<IEnumerable<Row>>

{

relation.Rows

};

foreach (ColumnOrdering columnOrdering in ColumnOrderings)

{

ColumnOrdering ordering = columnOrdering;

rowListLists =

rowListLists.SelectMany(rowList => GroupByRowList(rowList, ordering));

}

IEnumerable<Row> resultRows = rowListLists.SelectMany(x => x).Skip(SkipCount ?? 0);

return new Relation(FetchCount != null ? resultRows.Take(FetchCount.Value) : resultRows);

}

private static IEnumerable<IGrouping<string, Row>> GroupByRowList(IEnumerable<Row> rowList,

ColumnOrdering columnOrdering)

{

return OrderRowList(rowList, columnOrdering).GroupBy(GetCellValue(columnOrdering));

}

private static IEnumerable<Row> OrderRowList(IEnumerable<Row> rowList, ColumnOrdering columnOrdering)

{

return columnOrdering.Direction == OrderDirection.Ascending

? rowList.OrderBy(GetCellValue(columnOrdering))

: rowList.OrderByDescending(GetCellValue(columnOrdering));

}

private static Func<Row, string> 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));
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

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

}