public void Test2()
{
var sb = new SqlQuery();
sb
.Select
.Field(Order["ID"])
.Field(OrderItem.All)
.Field(OrderItem4["ID"])
.From
.Table(Order,
OrderItem.Join().Field(Order["ID"]).Equal.Field(OrderItem["OrderID"]),
OrderItem2.WeakJoin(),
OrderItem3.WeakJoin(
OrderItem5.WeakLeftJoin(),
OrderItem4.LeftJoin().Field(OrderItem4["ID"]).Equal.Field(OrderItem3["ID"]))
)
.Where
.Not.Field(Order["ID"]).Like("1234").Or
.Field(Order["ID"]).Equal.Value("!%")
.GroupBy
.Field(Order["ID"])
.OrderBy
.Field(Order["ID"])
;
Assert.AreEqual(1, sb.From.Tables.Count);
Assert.AreEqual(3, sb.From.Tables[0].Joins.Count);
Assert.AreEqual(2, sb.From.Tables[0].Joins[2].Table.Joins.Count);
sb.FinalizeAndValidate();
Assert.AreEqual(2, sb.From.Tables[0].Joins.Count);
Assert.AreEqual(1, sb.From.Tables[0].Joins[1].Table.Joins.Count);
}
private void HandleTable(Node?parent, SqlTable node, IEnumerable <string> prefix,
IResolveFieldContext context, ICollection <string> selections, ICollection <string> tables,
ICollection <WhereCondition> wheres, ICollection <string> orders, IEnumerable batchScope,
SqlCompilerContext compilerContext)
{
var arguments = node.Arguments;
// also check for batching
if (node.Paginate == false && (node.Batch == null || node.Junction?.Batch == null || parent == null))
{
if (node.Junction?.Where != null)
{
var whereBuilder = new WhereBuilder(_dialect.Quote(node.Junction.As), wheres);
node.Junction.Where.Invoke(whereBuilder, arguments, context, node);
}
// only add the where clause if there's no join or the join is not paginated
if (node.Where != null)
{
var whereBuilder = new WhereBuilder(_dialect.Quote(node.As), wheres);
node.Where.Invoke(whereBuilder, arguments, context, node);
}
}
if (ThisIsNotTheEndOfThisBatch(parent, node))
{
if (node.Junction?.OrderBy != null)
{
var junctionOrderBy = _dialect.CompileOrderBy(node.Junction.OrderBy);
orders.Add(junctionOrderBy);
}
if (node.OrderBy != null)
{
var orderBy = _dialect.CompileOrderBy(node.OrderBy);
orders.Add(orderBy);
}
// if (node.Junction?.SortKey != null)
// {
// var junctionOrderBy = _dialect.CompileOrderBy(node.Junction.SortKey);
// orders.Add(junctionOrderBy);
// }
//
// if (node.SortKey != null)
// {
// var orderBy = _dialect.CompileOrderBy(node.SortKey);
// orders.Add(orderBy);
// }
}
if (node.Join != null)
{
if (parent is SqlTable parentTable)
{
var join = new JoinBuilder(_dialect.Quote(parentTable.Name), _dialect.Quote(parentTable.As),
_dialect.Quote(node.Name), _dialect.Quote(node.As));
node.Join(join, arguments, context, node);
if (node.Paginate)
{
_dialect.HandleJoinedOneToManyPaginated(parentTable, node, arguments, context, tables, compilerContext,
join.Condition == null ? null : _dialect.CompileConditions(new[] { join.Condition }, compilerContext));
}
else if (join.Condition is RawCondition)
{
tables.Add($"{join.From} ON {_dialect.CompileConditions(new[] {join.Condition}, compilerContext)}");
}
else if (join.Condition != null)
{
tables.Add($"LEFT JOIN {_dialect.Quote(node.Name)} {_dialect.Quote(node.As)} ON {_dialect.CompileConditions(new[] {join.Condition}, compilerContext)}");
}
return;
}
}
else if (node.Junction?.Batch != null)
{
if (parent is SqlTable parentTable)
{
string columnName;
if (node.Junction.Batch.ParentKey.Expression != null)
{
columnName = node.Junction.Batch.ParentKey.Expression(_dialect.Quote(parentTable.As), node.Junction.Batch.ParentKey.Arguments, context, node);
}
else
{
columnName = $"{_dialect.Quote(parentTable.As)}.{_dialect.Quote(node.Junction.Batch.ParentKey.Name)}";
}
selections.Add($"{columnName} AS ${_dialect.Quote(JoinPrefix(prefix) + node.Junction.Batch.ParentKey.As)}");
}
else
{
var join = new JoinBuilder(_dialect.Quote(node.Name), _dialect.Quote(node.As), _dialect.Quote(node.Junction.Table), _dialect.Quote(node.Junction.As));
if (node.Junction.Batch.Join != null)
{
node.Junction.Batch.Join(join, arguments, context, node);
}
if (join.Condition == null)
{
throw new JoinMonsterException($"The 'batch' join condition on table '{node.Name}' for junction '{node.Junction.Table}' cannot be null.");
}
var joinCondition = _dialect.CompileConditions(new[] { join.Condition }, compilerContext);
if (node.Paginate)
{
_dialect.HandleBatchedManyToManyPaginated(parent, node, arguments, context, tables, batchScope.Cast <object>(),
compilerContext, joinCondition);
}
else
{
tables.Add($"FROM {_dialect.Quote(node.Junction.Table)} {_dialect.Quote(node.Junction.As)}");
tables.Add($"LEFT JOIN {_dialect.Quote(node.Name)} {_dialect.Quote(node.As)} ON {joinCondition}");
var column = _dialect.Quote(node.Junction.Batch.ThisKey.Name);
var whereBuilder = new WhereBuilder(_dialect.Quote(node.Junction.As), wheres);
if (node.Junction.Batch.Where != null)
{
node.Junction.Batch.Where(whereBuilder, column, batchScope, arguments, context, node);
}
else
{
whereBuilder.In(column, batchScope);
}
}
}
}
else if (node.Junction?.FromParent != null && node.Junction?.ToChild != null)
{
if (parent is SqlTable parentTable)
{
// TODO: Handle batching and paging
var fromParentJoin = new JoinBuilder(_dialect.Quote(parentTable.Name), _dialect.Quote(parentTable.As), _dialect.Quote(node.Junction.Table), _dialect.Quote(node.Junction.As));
var toChildJoin = new JoinBuilder(_dialect.Quote(parentTable.Name), _dialect.Quote(node.Junction.As), _dialect.Quote(node.Name), _dialect.Quote(node.As));
node.Junction.FromParent(fromParentJoin, arguments, context, node);
node.Junction.ToChild(toChildJoin, arguments, context, node);
if (fromParentJoin.Condition == null)
{
throw new JoinMonsterException($"The 'fromParent' join condition on table '{node.Name}' for junction '{node.Junction.Table}' cannot be null.");
}
if (toChildJoin.Condition == null)
{
throw new JoinMonsterException($"The 'toChild' join condition on table '{node.Name}' for junction '{node.Junction.Table}' cannot be null.");
}
var compiledFromParentJoinCondition = _dialect.CompileConditions(new[] { fromParentJoin.Condition }, compilerContext);
var compiledToChildJoinCondition = _dialect.CompileConditions(new[] { toChildJoin.Condition }, compilerContext);
if (node.Paginate)
{
// _dialect.HandleJoinedManyToManyPaginated();
}
else
{
if (fromParentJoin.Condition is RawCondition)
{
tables.Add($"{fromParentJoin.From} ON {compiledFromParentJoinCondition}");
}
else
{
tables.Add(
$"LEFT JOIN {_dialect.Quote(node.Junction.Table)} {_dialect.Quote(node.Junction.As)} ON {compiledFromParentJoinCondition}");
}
if (toChildJoin.Condition is RawCondition)
{
tables.Add($"{toChildJoin.From} ON {compiledToChildJoinCondition}");
}
else
{
tables.Add($"LEFT JOIN {_dialect.Quote(node.Name)} {_dialect.Quote(node.As)} ON {compiledToChildJoinCondition}");
}
}
}
return;
}
else if (node.Batch != null)
{
if (parent is SqlTable parentTable)
{
string columnName;
if (node.Batch.ParentKey.Expression != null)
{
columnName = node.Batch.ParentKey.Expression(_dialect.Quote(parentTable.As), node.Batch.ParentKey.Arguments, context, node);
}
else
{
columnName = $"{_dialect.Quote(parentTable.As)}.{_dialect.Quote(node.Batch.ParentKey.Name)}";
}
selections.Add($"{columnName} AS {_dialect.Quote(JoinPrefix(prefix) + node.Batch.ParentKey.As)}");
}
else if (node.Paginate)
{
_dialect.HandleBatchedOneToManyPaginated(parent, node, arguments, context, tables, batchScope.Cast <object>(), compilerContext);
}
else
{
tables.Add($"FROM {_dialect.Quote(node.Name)} AS {_dialect.Quote(node.As)}");
var column = _dialect.Quote(node.Batch.ThisKey.Name);
var whereBuilder = new WhereBuilder(_dialect.Quote(node.As), wheres);
if (node.Batch.Where != null)
{
node.Batch.Where(whereBuilder, column, batchScope, arguments, context, node);
}
else
{
whereBuilder.In(column, batchScope);
}
}
return;
}
else if (node.Paginate)
{
_dialect.HandlePaginationAtRoot(parent, node, arguments, context, tables, compilerContext);
return;
}
tables.Add($"FROM {_dialect.Quote(node.Name)} AS {_dialect.Quote(node.As)}");
}
/// <inheritdoc />
public override void HandleJoinedOneToManyPaginated(SqlTable parent, SqlTable node,
IReadOnlyDictionary <string, object> arguments, IResolveFieldContext context, ICollection <string> tables,
SqlCompilerContext compilerContext, string?joinCondition)
{
if (parent == null)
{
throw new ArgumentNullException(nameof(parent));
}
if (node == null)
{
throw new ArgumentNullException(nameof(node));
}
if (arguments == null)
{
throw new ArgumentNullException(nameof(arguments));
}
if (context == null)
{
throw new ArgumentNullException(nameof(context));
}
if (tables == null)
{
throw new ArgumentNullException(nameof(tables));
}
if (node.Join == null)
{
throw new JoinMonsterException($"{nameof(node)}.{nameof(node.Join)} on table '{node.Name}' cannot be null.");
}
var join = new JoinBuilder(Quote(parent.Name), Quote(parent.As), Quote(node.Name), Quote(node.As));
node.Join(join, arguments, context, node);
if (join.Condition == null)
{
throw new JoinMonsterException($"The join condition on table '{node.Name}' cannot be null.");
}
var pagingWhereConditions = new List <WhereCondition>
{
join.Condition
};
if (node.Where != null)
{
var whereBuilder = new WhereBuilder(Quote(node.As), pagingWhereConditions);
node.Where.Invoke(whereBuilder, arguments, context, node);
}
if (node.SortKey != null)
{
var(limit, order, whereCondition) = InterpretForKeysetPaging(node, arguments, context);
if (whereCondition != null)
{
pagingWhereConditions.Add(whereCondition);
}
tables.Add(KeysetPagingSelect(node.Name, pagingWhereConditions, order, limit, node.As, joinCondition, "LEFT", compilerContext));
}
else if (node.OrderBy != null)
{
var(limit, offset, order) = InterpretForOffsetPaging(node, arguments, context);
tables.Add(OffsetPagingSelect(node.Name, pagingWhereConditions, order, limit, offset,
node.As, joinCondition, "LEFT", compilerContext));
}
else
{
throw new JoinMonsterException("Cannot paginate without a SortKey or an OrderBy clause.");
}
}