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

Today we implement grouping. Since we can group in multiple ways, we need to have grouping sets:

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

namespace QueryLogic.Grouping
{
    public class GroupingSet
    {
        public GroupingSet(IEnumerable columns)
        {
            Columns = columns;
        }

        public IEnumerable Columns { get; private set; }

        protected bool Equals(GroupingSet other)
        {
            return Columns.SequenceEqual(other.Columns);
        }

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

        public override int GetHashCode()
        {
            return (Columns != null ? Columns.GetHashCode() : 0);
        }
    }
}

Grouping set is just a set of columns which we group by. Next comes the operator:

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

namespace QueryLogic.Grouping
{
    public class GroupBy
    {
        public GroupBy(IRelationProvider sourceRelationProvider, IEnumerable columnSets)
        {
            SourceRelationProvider = sourceRelationProvider;
            ColumnSets = columnSets;
        }

        public GroupBy(IRelationProvider sourceRelationProvider, IEnumerable columns)
            : this(sourceRelationProvider, new[] { new GroupingSet(columns) })
        {
        }

        public IRelationProvider SourceRelationProvider { get; private set; }
        public IEnumerable ColumnSets { get; private set; }

        protected bool Equals(GroupBy other)
        {
            return Equals(SourceRelationProvider, other.SourceRelationProvider) &&
                   ColumnSets.SequenceEqual(other.ColumnSets);
        }

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

        public override int GetHashCode()
        {
            unchecked
            {
                return ((SourceRelationProvider != null ? SourceRelationProvider.GetHashCode() : 0) * 397) ^
                       (ColumnSets != null ? ColumnSets.GetHashCode() : 0);
            }
        }

        public IEnumerable CreateRelations(Node source)
        {
            return ColumnSets.SelectMany(g => CreateRelation(g, source));
        }

        private IEnumerable CreateRelation(GroupingSet groupingSet, Node source)
        {
            IEnumerable> rowListsList = new List>
            {
                SourceRelationProvider.CreateRelation(source).Rows
            };

            foreach (ColumnHeader column in groupingSet.Columns)
            {
                rowListsList = rowListsList.SelectMany(rowList => rowList.GroupBy(row => row.GetCellValue(column)));
            }

            foreach (var rowsList in rowListsList)
            {
                var result = new Relation();
                result.AddRows(rowsList);
                yield return result;
            }
        }
    }
}

And here are the tests:

using System.Collections.Generic;
using Model;
using NUnit.Framework;
using QueryLogic.Grouping;
using QueryLogic.Test.Mocks;

namespace QueryLogic.Test.Grouping
{
    [TestFixture]
    public class GroupByTests
    {
        [Test]
        public void CreateRelation_OneGroupSetPassed_ShouldReturnOneGroup()
        {
            // Arrange
            var expected = new Relation(new[]
            {
                new Row(new[]
                {
                    new Cell("schema", "A", "value")
                }),
                new Row(new[]
                {
                    new Cell("schema", "A", "value")
                }),
                new Row(new[]
                {
                    new Cell("schema", "A", "value")
                })
            });

            var groupBy = new GroupBy(new DummyRelationProvider(expected), new[] { new ColumnHeader("schema", "A") });

            // Act
            IEnumerable actual = groupBy.CreateRelations(null);

            // Assert
            CollectionAssert.AreEquivalent(new[] { expected }, actual);
        }

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

            var groupBy = new GroupBy(new DummyRelationProvider(source),
                new[] { new ColumnHeader("schema", "A"), new ColumnHeader("schema", "B") });

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

            // Act
            IEnumerable actual = groupBy.CreateRelations(null);

            // Assert
            CollectionAssert.AreEquivalent(expected, actual);
        }

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

            var groupBy = new GroupBy(new DummyRelationProvider(source), new[]
            {
                new GroupingSet(new[] {new ColumnHeader("schema", "A"), new ColumnHeader("schema", "B")}),
                new GroupingSet(new[] {new ColumnHeader("schema", "A")})
            });

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

            // Act
            IEnumerable actual = groupBy.CreateRelations(null);

            // Assert
            CollectionAssert.AreEquivalent(expected, actual);
        }

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

            var groupBy = new GroupBy(new DummyRelationProvider(source), new[] { new ColumnHeader("schema", "A") });

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

            // Act
            IEnumerable actual = groupBy.CreateRelations(null);

            // Assert
            CollectionAssert.AreEquivalent(expected, actual);
        }
    }
}