Fluent Query API

Continuing the discussion from Query execute argument horror:

The above says it all. There are several examples of Fluent/LINQ type querying in other languages from which inspiration could be drawn concerning the API spec.

Because the Query.cfc is cfml based it is not a big deal to extend it that way, maybe the best is to do a prototype. In other word extend the existing Query.cfc

A nice way to support “builder” style interfaces like that is to use a struct containing closures (so that each part of the builder can return an appropriate set of “next” legal functions). I just implemented that in FW/1 for the renderData() fluent API.

And, yes, I agree a prototype of this would be worth taking a look at if you want to create it.

BTW, I probably would not go the whole route of a SQL builder syntax (that’s a lot of work and introduces the potential for a lot of bugs – and not everyone will agree with whatever syntax you actually pick), but a builder for the various Query.cfc parameters and sub-options would be reasonable:

result = new Query().sql(myStatement).datasource(myDSN).parameters(myParamArray).execute();

with perhaps this as a shorthand:

result = new Query().sql(myStatement).datasource(myDSN).execute(myParamArray);

(and support for all the other parts of options as builder functions).

I’d be all for them doing something like LINQ, but I’d want them to deal with it more broadly than just with queries. The whole thing with LINQ is it works on any type of data collection. It’s that “ubiquity” that makes it useful.

From just an isolated query perspective, what you are suggesting is a helluva lot more clumsy that just passing an SQL statement with queryExecute().

Re LINQ, yeah, down the road, I would like to see this turn into that sort of system.

Re the “clumsy” nature, it does seem that way if you’re just trying to do a quick-n-dirty SQL query. However, depending on how it is designed, it could give you some nice options, for instance a query service:

ProductsService = Query::using(datasource).from("table").select("*");

productList       = ProductsService.execute();
expensiveProducts = ProductsService.where("cost", ">", expensiveThreshold).execute();
productNames      = ProductsService.select("name").execute();

That’s not so “clumsy” IMO :wink:

Or you could simply:

allProducts = queryExecute("SELECT * FROM table");
expensiveProducts = queryExecute(
    "SELECT name FROM allProducts WHERE cost > ?",
    [expensiveThreshold],
    {dbtype="query", allProducts=allProducts} // this is a bit weak, I admit
);

I don’t measure code clarity by “less typing”, but that’s less typing.

It also sticks closer to understood technologies (existing CFML, SQL) than your example, which - if I’m honest - just looks like someone trying to reinvent SQL but at the same time making it even more awful. I mean - for goodness sake - you just suggested this:

ProductsService.where("cost", ">", expensiveThreshold)

Objectmethodstring… another string (for an operator!)… value to compare to the first string using the second string.

Yer having a laugh, right? Living up to the “jester” part of your account name.

Right?

Does the monkey appear to be laughing to you? :stuck_out_tongue:

Seriously though, I get what you’re saying. I’m not trying to spell out the arguments of the method - I just grabbed a quick example from somewhere ( Query Builder - Laravel - The PHP Framework For Web Artisans I believe ). What the methods are called or what arguments they accept isn’t what is interesting me. It’s that type of system and the types of design patterns it affords that interests me. To the dismay of @seancorfield, I think what I’m wanting really is a full on SQL Builder, although this whole discussion is the proof behind his caution against trying to build such a system.

And again, for the simple isolated queries, sure - I would agree, queryExecute is purpose-built. But if what you are after is a robust service layer, you could either write out a robust service layer with all your queries…or write a query builder that does it for you. :smiley:

Edit concerning:

well… an arbitrary set of understood technologies anyways. I’d argue query builders are also an understood technology:

http://maxtoroq.github.io/DbExtensions/
LINQ to SQL: .NET Language-Integrated Query for Relational Data | Microsoft Learn (although this is implemented as actual language syntax…)
http://lichtner.github.io/fluentpdo/
Pixie - A Database Query Builder for PHP

You’re now describing ORM, and HQL. No need to reinvent that either.

And all of those - except for LINQ - are a bit shit.

Anyway, what I meant - and I think you were willfully choosing to not understand this - was the technology of SQL is “understood” in the sense that SQL is ubiquitous in our industry. And it is a standard. What you have demonstrated above is four (I’m excluding LINQ) different ways of skinning the same cat, except in a clumsy, proprietary way.

I was not suggesting other ppl haven’t already come up with their own proprietary implementations of such things: I was not thinking “whoah! dajester2013 has come up with a really unique idea here!”

Yer just repeating one that’s misguided, IMO.

Note: I don’t mind the idea of a fluent API at all: that’s cool, and I’m all for that. But you went off the rails with your suggested use-case.

Still: mileage varies, and it’s not me you need to convince. Go yer hardest.