Recursion in Fluent API

Question

I am designing a fluent API for writing SQL. Keep in mind one of my goals is to have API not suggest functions that can't be called in that part of the chain. For instance if you just got done defining a field in the select clause you can't call Where until you called From first. A simple query looks like this:

        string sql = SelectBuilder.Create()
            .Select()
                .Fld("field1")
            .From("table1")
            .Where()
                .Whr("field1 > field2")
                .Whr("CURRENT_TIMESTAMP > field3")
            .Build()
            .SQL;

My problem comes with recursion in SQL code. Say you wanted to have a field contain another SQL statement like below:

        string sql = SelectBuilder.Create()
            .Select()
                .Fld("field1")
                .SQLFld()
                    .Select
                        .Count("field6")
                    .From("other table")
                .EndSQLFld()
                .FLd("field2")
            .From("table1")
            .Where()
                .Whr("field1 > field2")
                .Whr("CURRENT_TIMESTAMP > field3")
            .Build()
            .SQL;

I am using method chaining to build my fluent API. It many ways it is a state machine strewn out across many classes which represent each state. To add this functionality I would need to copy essentially every state I already have and wrap them around the two SQLFld and EndSQLFld states. I would need yet another copy if you were one more level down and were embedding a SQL statement in to a field of the already embedded SQL statement. This goes on to infinity, so with an infinitely deep embedded SQL query I would need an infinite number of classes to represent the infinite states.

I thought about writing a SelectBuilder query that was taken to the point of the Build method and then embedding that SelectBuilder in to another SelectBuilder and that fixes my infinity problem, but it is not very elegant and that is the point of this API.

I could also throw out the idea that the API only offers functions when they are appropriate but I would really hate to do that. I feel like that helps you best discover how to use the API. In many fluent APIs it doesn't matter which order you call what, but I want the API to appear as close to the actual SQL statement as possible and enforce its syntax.

Anyone have any idea how to solve this issue?

And here I thought you were talking about recursive CTEs. Note that subqueries can contain references to the outer environment (which is the _normal_ case, actually). Subqueries can also be used as table-references (`FROM`/`JOIN` clauses), and in the `WHERE` clause. I hope you're not just concatenating the strings together, as people might otherwise expect this to be SQL Injection-proof (so at minimum, you need to provide a variable-parameterizing method). Of course, C# already has Linq-to-SQL, although it's a bit different. — Clockwork-Muse, Feb 16 '14 at 00:14

Adrian Padilla · Accepted Answer · 2014-02-17T05:25:42.047

Glad to see you are trying fluent interfaces, I think they are a very elegant and expressive.

The builder pattern is not the only implementation for fluent interfaces. Consider this design, and let us know what you think =)

This is an example and I leave to you the details of your final implementation.

Interface design example:

 public class QueryDefinition
    {
        // The members doesn't need to be strings, can be whatever you use to handle the construction of the query.
        private string select;
        private string from;
        private string where;

        public QueryDefinition AddField(string select)
        {
            this.select = select;
            return this;
        }

        public QueryDefinition From(string from)
        {
            this.from = from;
            return this;
        }

        public QueryDefinition Where(string where)
        {
            this.where = where;
            return this;
        }

        public QueryDefinition AddFieldWithSubQuery(Action<QueryDefinition> definitionAction)
        {
            var subQueryDefinition = new QueryDefinition(); 
            definitionAction(subQueryDefinition);

            // Add here any action needed to consider the sub query, which should be defined in the object subQueryDefinition.

            return this;
        }

Example usage:

static void Main(string[] args)
        {
            // 1 query deep
            var def = new QueryDefinition();
            def
                .AddField("Field1")
                .AddField("Filed2")
                .AddFieldWithSubQuery(subquery =>
                {
                    subquery
                        .AddField("InnerField1")
                        .AddField("InnerFiled2")
                        .From("InnerTable")
                        .Where("<InnerCondition>");
                })
                .From("Table")
                .Where("<Condition>");


            // 2 queries deep
            var def2 = new QueryDefinition();
            def2
                .AddField("Field1")
                .AddField("Filed2")
                .AddFieldWithSubQuery(subquery =>
                {
                    subquery
                        .AddField("InnerField1")
                        .AddField("InnerField2")
                        .AddFieldWithSubQuery(subsubquery =>
                            {
                                subsubquery
                                    .AddField("InnerInnerField1")
                                    .AddField("InnerInnerField2")
                                    .From("InnerInnerTable")
                                    .Where("<InnerInnerCondition>");
                            })
                        .From("InnerInnerTable")
                        .Where("<InnerCondition>");
                })
                .From("Table")
                .Where("<Condition>");
        }

Well played. It looks pretty good and solves the problem perfectly. I thought about using lamdas for other reasons but they fit this problem as well. — Greg Cobb, Feb 16 '14 at 05:12

score 0 · Answer 2 · answered Feb 16 '14 at 01:08

You can't "have only applicable methods available" without either sub-APIs for the substructures or clear bracketing/ending of all inner structural levels (SELECT columns, expressions in WHERE clause, subqueries).

Even then, running it all through a single API will require it to be stateful & "modal" with "bracketing" methods, to track whereabouts in the decl you are. Error reporting & getting these right will be tedious.

Ending bracketing by "fluent" methods, to me, seems non-fluent & ugly. This would result in a ugly appearence of EndSelect, EndWhere, EndSubquery etc. I'd prefer to build substructures (eg SUBQUERY for select) into a local variable & add that.

I don't like the EndSQLFld() idiom, which terminates the Subquery implicitly by terminating the Field. I'd prefer & guess it would be better design to terminate the subquery itself which is the complex part of the nested structure -- not the field.

To be honest, trying to enforce ordering of a "declarative" API for a "declarative" language (SQL) seems to be a waste of time.

Probably what I'd consider closer to an ideal usage:

SelectBuilder select = SelectBuilder.Create("CUSTOMER")
        .Column("ID")
        .Column("NAME")
        /*.From("CUSTOMER")*/      // look, I'm just going to promote this onto the constructor.
        .Where("field1 > field2")
        .Where("CURRENT_TIMESTAMP > field3");

SelectBuilder countSubquery = SelectBuilder.Create("ORDER")
        .Formula("count(*)");
        .Where("ORDER.FK_CUSTOMER = CUSTOMER.ID");
        .Where("STATUS = 'A'");

select.Formula( countSubquery, "ORDER_COUNT");
string sql = SelectBuilder.SQL;

Apologies to the Hibernate Criteria API :)

Recursion in Fluent API

2 Answers2