public void Compile_WithJunctionOrderBy_SqlShouldIncludeJoinAndOrderByClause()
{
var schema = CreateSimpleSchema(builder =>
{
builder.Types.For("Product")
.SqlTable("products", "id");
builder.Types.For("Product")
.FieldFor("relatedProducts", null)
.SqlJunction("productRelations",
(join, _, __, ___) => join.On("id", "productId"),
(join, _, __, ___) => join.On("relatedProductId", "id"))
.OrderBy((order, _, __, ___) => order.By("productId"));
});
var query = "{ product { name, relatedProducts { name } } }";
var context = CreateResolveFieldContext(schema, query);
var converter = new QueryToSqlConverter(new DefaultAliasGenerator());
var compiler = new SqlCompiler(new SqlDialectStub());
var node = converter.Convert(context);
var sql = compiler.Compile(node, context);
sql.Sql.Should().Be("SELECT\n \"product\".\"id\" AS \"id\",\n \"product\".\"name\" AS \"name\",\n \"relatedProducts\".\"id\" AS \"relatedProducts__id\",\n \"relatedProducts\".\"name\" AS \"relatedProducts__name\"\nFROM \"products\" AS \"product\"\nLEFT JOIN \"productRelations\" \"productRelations\" ON \"product\".\"id\" = \"productRelations\".\"productId\"\nLEFT JOIN \"products\" \"relatedProducts\" ON \"productRelations\".\"relatedProductId\" = \"relatedProducts\".\"id\"\nORDER BY \"relatedProducts\".\"productId\" ASC");
}
public void Compile_WithJoinAndPaginate_SqlShouldIncludePaginationClause()
{
var schema = CreateSimpleSchema(builder =>
{
builder.Types.For("Product")
.SqlTable("products", "id");
builder.Types.For("ProductVariant")
.SqlTable("productVariants", "id");
builder.Types.For("Product")
.FieldFor("variants", null)
.SqlJoin((join, _, __, ___) => join.On("id", "productId"))
.SqlPaginate(true)
.SqlOrder((order, _, __, ___) => order.By("id"));
});
var query = "{ product { name, variants { edges { node { name } } } } }";
var context = CreateResolveFieldContext(schema, query);
var joinedOneToManyPaginatedSql = "LEFT JOIN LATERAL (\n SELECT \"variants\".*, COUNT(*) OVER () AS \"$total\"\n FROM \"productVariants\" \"variants\"\n WHERE \"products\".\"id\" = \"variants\".\"productId\"\n ORDER BY \"variants\".\"id\" ASC\n LIMIT ALL OFFSET 0\n) \"variants\" ON \"products\".\"id\" = \"variants\".\"productId\"";
var converter = new QueryToSqlConverter(new DefaultAliasGenerator());
var dialect = new SqlDialectStub(joinedOneToManyPaginatedSql: joinedOneToManyPaginatedSql);
var compiler = new SqlCompiler(dialect);
var node = converter.Convert(context);
var sql = compiler.Compile(node, context);
sql.Sql.Should().Be($"SELECT\n \"product\".\"id\" AS \"id\",\n \"product\".\"name\" AS \"name\",\n \"variants\".\"id\" AS \"variants__id\",\n \"variants\".\"name\" AS \"variants__name\",\n \"variants\".\"$total\" AS \"variants__$total\"\nFROM \"products\" AS \"product\"\n{joinedOneToManyPaginatedSql}\nORDER BY \"variants\".\"id\" ASC");
}
public void Compile_WithWhereAndOrderBy_GeneratesValidSQL()
{
var schema = CreateSimpleSchema(builder =>
{
builder.Types.For("Product")
.SqlTable("products", "id");
builder.Types.For("Query")
.FieldFor("products", null)
.SqlWhere((where, _, __, ___) => where.Column("id", 0, "<>"))
.SqlOrder((order, _, __, ___) => order.By("name").ThenByDescending("price"));
});
var query = "{ products { name } }";
var context = CreateResolveFieldContext(schema, query);
var converter = new QueryToSqlConverter(new DefaultAliasGenerator());
var compiler = new SqlCompiler(new SqlDialectStub());
var node = converter.Convert(context);
var sql = compiler.Compile(node, context);
var expectedSql =
"SELECT\n \"products\".\"id\" AS \"id\",\n \"products\".\"name\" AS \"name\"\nFROM \"products\" AS \"products\"\nWHERE \"products\".\"id\" <> @p0\nORDER BY \"products\".\"name\" ASC, \"products\".\"price\" DESC";
var expectedParameters = new Dictionary <string, object> {
{ "@p0", 0 }
};
sql.Should()
.BeEquivalentTo(new SqlResult(expectedSql, expectedParameters));
}
public void Compile_WithRawJoinCondition_SqlShouldIncludeJoinCondition()
{
var schema = CreateSimpleSchema(builder =>
{
builder.Types.For("Query")
.FieldFor("product", null);
builder.Types.For("Product")
.SqlTable("products", "id");
builder.Types.For("Product")
.FieldFor("variants", null)
.SqlJoin((join, _, __, ___) => join.Raw($"{join.ParentTableAlias}.\"id\" = {@join.ChildTableAlias}.\"productId\"", @from: $"LEFT JOIN {join.ChildTableName} {join.ChildTableAlias}"));
builder.Types.For("ProductVariant")
.SqlTable("productVariants", "id");
});
var query = "{ product { name, variants { edges { node { name } } } } }";
var context = CreateResolveFieldContext(schema, query);
var converter = new QueryToSqlConverter(new DefaultAliasGenerator());
var compiler = new SqlCompiler(new SqlDialectStub());
var node = converter.Convert(context);
var sql = compiler.Compile(node, context);
sql.Sql.Should().Be("SELECT\n \"product\".\"id\" AS \"id\",\n \"product\".\"name\" AS \"name\",\n \"variants\".\"id\" AS \"variants__id\",\n \"variants\".\"name\" AS \"variants__name\"\nFROM \"products\" AS \"product\"\nLEFT JOIN \"productVariants\" \"variants\" ON \"product\".\"id\" = \"variants\".\"productId\"");
}
public void Compile_WithWhereQueryAndArguments_PassesArgumentsToWhereDelegate()
{
var schema = CreateSimpleSchema(builder =>
{
builder.Types.For("Product")
.SqlTable("products", "id");
builder.Types.For("Query")
.FieldFor("product", null)
.SqlWhere((where, arguments, _, __) => where.Column("id", arguments["id"]));
});
var query = "{ product(id: \"3\") { name } }";
var context = CreateResolveFieldContext(schema, query);
var converter = new QueryToSqlConverter(new DefaultAliasGenerator());
var compiler = new SqlCompiler(new SqlDialectStub());
var node = converter.Convert(context);
var sql = compiler.Compile(node, context);
var expectedSql = "SELECT\n \"product\".\"id\" AS \"id\",\n \"product\".\"name\" AS \"name\"\nFROM \"products\" AS \"product\"\nWHERE \"product\".\"id\" = @p0";
var expectedParameters = new Dictionary <string, object> {
{ "@p0", "3" }
};
sql.Should().BeEquivalentTo(new SqlResult(expectedSql, expectedParameters));
}
/// <summary>
/// Creates a new instance of <see cref="JoinMonsterExecuter"/>.
/// </summary>
/// <param name="converter">The <see cref="QueryToSqlConverter"/>.</param>
/// <param name="compiler">The <see cref="ISqlCompiler"/>.</param>
/// <param name="batchPlanner">The <see cref="IBatchPlanner"/>.</param>
/// <param name="hydrator">The <see cref="Hydrator"/>.</param>
public JoinMonsterExecuter(QueryToSqlConverter converter, ISqlCompiler compiler, IBatchPlanner batchPlanner, Hydrator hydrator)
{
_converter = converter ?? throw new ArgumentNullException(nameof(converter));
_compiler = compiler ?? throw new ArgumentNullException(nameof(compiler));
_batchPlanner = batchPlanner ?? throw new ArgumentNullException(nameof(batchPlanner));
_hydrator = hydrator ?? throw new ArgumentNullException(nameof(hydrator));
_objectShaper = new ObjectShaper(new SqlAstValidator());
_arrayToConnectionConverter = new ArrayToConnectionConverter();
}
public void Compile_WhenTableConfigHasCompositeUniqueKey_KeysAreIncludedInGeneratedSql()
{
var schema = CreateSimpleSchema(builder =>
{
builder.Types.For("Product")
.SqlTable("products", new [] { "id", "firstName", "lastName" });
});
var query = "{ product { name } }";
var context = CreateResolveFieldContext(schema, query);
var converter = new QueryToSqlConverter(new DefaultAliasGenerator());
var compiler = new SqlCompiler(new SqlDialectStub());
var node = converter.Convert(context);
var sql = compiler.Compile(node, context);
sql.Sql.Should().Be("SELECT\n CONCAT(\"product\".\"id\", \"product\".\"firstName\", \"product\".\"lastName\") AS \"id#firstName#lastName\",\n \"product\".\"name\" AS \"name\"\nFROM \"products\" AS \"product\"");
}
public void Compile_WhenQueryIncludesUniqueKey_ColumnIsOnlySelectedOnce()
{
var schema = CreateSimpleSchema(builder =>
{
builder.Types.For("Product")
.SqlTable("products", "id");
});
var query = "{ product { id, name } }";
var context = CreateResolveFieldContext(schema, query);
var converter = new QueryToSqlConverter(new DefaultAliasGenerator());
var compiler = new SqlCompiler(new SqlDialectStub());
var node = converter.Convert(context);
var sql = compiler.Compile(node, context);
sql.Sql.Should().Be("SELECT\n \"product\".\"id\" AS \"id\",\n \"product\".\"name\" AS \"name\"\nFROM \"products\" AS \"product\"");
}
public void Compile_WithMinifyAliasGenerator_SqlHasMinifiedTableAndColumnNames()
{
var schema = CreateSimpleSchema(builder =>
{
builder.Types.For("Product")
.SqlTable("products", "id");
});
var query = "{ product { id, name } }";
var context = CreateResolveFieldContext(schema, query);
var converter = new QueryToSqlConverter(new MinifyAliasGenerator());
var compiler = new SqlCompiler(new SqlDialectStub());
var node = converter.Convert(context);
var sql = compiler.Compile(node, context);
sql.Sql.Should().Be("SELECT\n \"a\".\"id\" AS \"b\",\n \"a\".\"name\" AS \"c\"\nFROM \"products\" AS \"a\"");
}
public void Compile_WithOrderBy_SqlShouldIncludeOrderByClause()
{
var schema = CreateSimpleSchema(builder =>
{
builder.Types.For("Product")
.SqlTable("products", "id");
builder.Types.For("Query")
.FieldFor("products", null)
.SqlOrder((order, _, __, ___) => order.By("name").ThenByDescending("price"));
});
var query = "{ products { name } }";
var context = CreateResolveFieldContext(schema, query);
var converter = new QueryToSqlConverter(new DefaultAliasGenerator());
var compiler = new SqlCompiler(new SqlDialectStub());
var node = converter.Convert(context);
var sql = compiler.Compile(node, context);
sql.Sql.Should().Be("SELECT\n \"products\".\"id\" AS \"id\",\n \"products\".\"name\" AS \"name\"\nFROM \"products\" AS \"products\"\nORDER BY \"products\".\"name\" ASC, \"products\".\"price\" DESC");
}
