http://coldfusion.com logo
Join Slack
Powered by
<@U070SQMD1> in lucee6 in qoq will we be able to h...
# lucee
d
@zackster in lucee6 in qoq will we be able to have custom functions on the model (row data) level of a query (or store)?
z
i have been musing over this with @bdw429s
d
#please
b
I'm curious what you have in mind
I'm not sure if it's the same thing Zac and I duscussed
Do you mean like member functions on a query row or calling cf functions via qoq
There is a ticket that fleshes out the latter in JIRA
👍 1
a
I would def like to see a fr'instance on this. Also to know what @bdw429s & @zackster have been talking about. Outwardly this sounds... ill-conceived. But having put that judgement out there, am prepared to revoke it for a good idea 😉 (although "a good idea involving QoQ" is something I'm having trouble getting my brain around 😉)
z
hahah, thanks @Adam Cameron, even more motivation for Brad and I to improve QoQs even further 🙂
can't find the ticket but the general idea was to expose the cfml namespace as a schema with functions
select cf.listFirst(fullname, ' ' ) as firstname from users
etc
but we were still bouncing around what the best syntax / namespace might be last time we discussed it
a
why would one not use 
Query.map
for that? My chief hesitation is that "passing a string to something to process" doesn't seem like a great wat to write code. I mean with a DB it makes sense cos SQL is the lingua franca, but doing it to Lucee itself? Not a great way of solving whatever the perceived problem is.
If I was to be enhancing the QoQ DSL, I'd be implementing the ability to join queries. I'd not be implementing stuff that can already be done with CFML, and done in a tidier, more idiomatic, way.
Focus on stuff that can't be done at all now. Don't create a second way of doing something, when there's already a better way of doing it.
z
Also discussed but far more complex to implement
the rationale was partly that QoQ is missing some basic common sql functions, so rather than re-invent the wheel, this approach easily leverages existing functionality
a
When thinking about this... I was thinking 
UNION
might be something that QoQ does that has no one-expression equiv in native CFML? How might that be implemented I wonder? My 30sec of thought on the topic didn't come up with anything else that can't already be done "better" using CFML these days (with the map / reduce / sort / filter methods)
the rationale was
Yeah, got it. But... are you in the business of writing an SQL dialect? No.
(Am running devil's advocate here as much as disagreeing, btw)
z
i know :)
it's nice to have different ways to skin a cat
i'd be more interested in update support myself
a
Also discussed but far more complex to implement
Sure. But that's what we need the Lucee Dev Team to do. Because the dev users of Lucee can't do that themselves already.
z
this is basically all work which brad's clients might fund
a
it's nice to have different ways to skin a cat
If you have spare time on yer hands maybe. But I don't think it's a good use of the amount of time you have. YMMV obvs.
z
see above!
a
Gotcha. Are they gonna fund the perpetual support for it for the life of the product? Probably not. So... be careful. Still don't think stuff like 
select cf.listFirst(fullname, ' ' ) as firstname from users
is compelling. I think Brad's client might be trying to solve their perceived issue in a subpar way.
(I realise that was just an off-the-cuff example)
I would wanna see a better use case than that before I would be willing to entertain putting something into Lucee to accommodate their whim.
Now if one was to expose the mechanations of the Lucee SQL interpretor so they [or anyone] could write their own extensions... (sounds like a lot of work, that said)
b
To be clear, my client who has funded some of my QoQ work in the past hasn't asked for better joins nor for being able to call CF functions directly in QoQ via a namespace. The former is just something I've mapped out in my head and would like to tackle if I can get the time. The latter is something Zac and i were throwing around as it would be fairly easy to implement and I think could add quite a lot of value. Basically, there are a whole bunch of DB-specific functions that MySQL, SQLServer, Postres, Oracle, etc implement and I regularly come across some I think, "Hey, that would be easy and handy to have in QoQ" but the question becomes, do you use the MySQL flavor, or the SQL Server flavor... or perhaps an even more familiar and shared syntax-- the CFML flavor! A specific example was regex matching which is very handy in where clauses, but no DB agrees on how to implement it. I could pick on DB to copy (and everyone who doesn't use that DB will complain) or I could go with something already idiomatic to CFML developers and have 
cf.refindNoCase( ".*foo$", col1 )
Then the thought became-- well why not add all the basic math/string/date functions that CFML has available via a cf. namespace in QoQ? (Side note, I already did something similar in the CommandBox CLI)
Copy code
CommandBox> #hash pass | #ucase | #reverse
which provides direct access to CFML functions from the command line via a command parsing syntax
As far as being able to do it with map/reduce, etc, that's generally true, however it's more code IMO and a lot of clients already have existing investments into QoQ in their code which is familiar and easy for them to read. Being able to format a date directly in your QoQ is one less step (loop) and less code than writing a map to do it.
As far as the effort to do it-- the core Lucee team is loathe to use let alone dev on QoQ so they're not going to spend any time on it. It's something I find interesting and I can spend my occaisional free time improving QoQ at no cost to the time of the core dev team other than them merging my changes and passing unit tests.
Now, in the department of 'should you do it', I do have some apprehensions on the cf namespace-- namely that I DON'T want to get into the business of having CFML intermingled with SQL. I think that would be CFClient JS mixes with CFML all over again and quite confusing. For instance, I would be leery of trying to support any sort of native CF function that accepts a UDF/closure. Ex
Copy code
SELECT listMap( colName, (i)=>ucase(i) ), anotherCol
from myQry
That would be confusing and much complicated to be directly mixing the different langauges. So I'd want to limit the integration to be functions operating on simple values (though I DO have some ideas about native JSON support...) which can have an analogous mapping from the generic 
func( arg, arg )
sql syntax to the same CFML syntax.
So I would draw the line that it is a SQL function using SQL rules and SQL syntax proxying to CF function of the same name.
It WOULD be possible and somewhat easy to have this
Copy code
var customFunc = (s)=>rerverse(s);
queryExecute( "
  select col1, col2, cfudf.customFunc( col3 ) as myAlias
", [], { dbType='query' } )
but I would be rather cautious about taking it that far. I think there's a balance of adding something incrementally useful to existing QoQ SQL and blurring the lines a bit between your CFML and SQL.
Anyway, I would prefer to add join support first as well (and I'd want to do the 
INNER JOIN
syntax as well as the 
from tbl1, tb2
syntax as well as left, right, and full outer joins while I was at it. (It always annoyed me CF didn't have outer joins!) While I've thought a good deal about it, no one has offered to sponsor it so I need to have quite a bit of free time to drop on it (not all dev time is fungible) so it's been easier to pick at a few low hanging fruit that I can button up in a day or two thus far.
a
That's a lot of useful information and I'm gonna come back to it later (I've been watching rugby and drinking beer, and I CBA getting my brain to focus). But on this:
I do have some apprehensions on the cf namespace
Yeah. Because if you do that, you gotta support everything that CFML can do, in a SQL-ish sort of way. Yikes. How about you just raise tickets for adding in "have a date formatting function like how mysql does", and "have a regex replace function like how pgsql has". Or maybe settle on one SQL dialect to follow etc. Implement some stuff that solves some real pain points (re the dateformatting... I'd be going "no, fuck that... you can do that in yer presentation later, or whatever intermediary adapter you need to make a date into a string" etc etc). And I'd still err on the side of "look: just... no. The way you want do to this is just not how it should be done at a language level, even if it's convenient for the way you've written your code... do it in the DB or do it on the app code... don't try to do it halfway in between". Your clients might have the perfect sensible use case where 
DATEFORMAT
makes sense to be done inside a QoQ. But you also need to look at the can of worms yer opening if you put it into Lucee. That's gonna be abused, and it's gonna make for a lot of shit CFML out there, which at some point you or I might need to maintain. Every paid-for feature into Lucee should first and foremost benefit & be a good fit for Lucee. Not the paying client. And the paying client should also be paying enough that any future maintenance of said feature is covered. For the life of the product.
b
I've been watching rugby
I'm watching an NFL preseason game 🙂
a
And now I have pasta to eat / more beer to drink (I never claimed to be a good judge of intake of such things) / and raiders / supermutants to shoot.
b
you gotta support everything that CFML can do
Not not necessarily true. We can implement whatever sub set makes sense and then clearly document it 🙂 Just because there exists a slippery slope doesn't mean we can't avoid it.
"look: just... no. The way you want do to this is just not how it should be done at a language level, even if it's convenient for the way you've written your code... do it in the DB or do it on the app code... don't try to do it halfway in between".
I understand what you're saying, I just have a different perspective I think. I think T-SQL, P-SQL and the like have shown us there's great use cases and uptake on using SQL to not just select and aggregate, but also to manipulate data in a set-based syntax. I'm of the mind that QoQ has always been a bit of a red-head step child and there's no reason it should be. SQL syntax is a familiar and powerful way to transform set-based data and I'd like to make it kick butt by asking "_why not!?_" more often.
Not the paying client
I agree with you there, but paying clients have nothing to do with the motive for these features. I think there's an envelope that can be pushed here in a way that makes sense. I've entered in ticket to the Adobe bug base for every improvement I've made to Lucee's QoQ and I'd invite Adobe to follow here as well. Also, FWIW, the cf namespace wasn't entirely cooked up in a smokey backroom by Zac and myself. We had a large RCF thread back in Feb of this year about it with lots of examples and opinions shared. https://dev.lucee.org/t/request-for-comments-new-proposed-qoq-feature-for-cfml-bifs/9609
a
All good, just on this:
I just have a different perspective I think. I think T-SQL, P-SQL and the like have shown us there's great use cases [...]
I'll go back to my earlier comment about basically "writing code in a string" here, I think. It just seems like a shit way of solving a problem. To me. IMO. etc. Other platforms / approaches have been to eschew the whole SQL string thing, instead using DSLs (LINQ etc). But here we're thinking of charging further in to using code in strings... (slightly different use case, I know; the metaphor stands though, I think) Also... almost all CFMLers are shit at CFML, and even worse @ SQL. I can see how this is gonna have minimal uptake by ppl who could actually use it properly, and fuck-load of questions here and on other Lucee forums of ppl not being able to maker their code^h^h^h^hstring work(\*). I see this as a kind of "just because you can, doesn't mean you should". But anyway... you've articulated yer opinion well and I get what yer saying. I don't agree it's a good feature, but... like... "me" and "disagreeing" isn't exactly a new concept around here 😉 (\*) error reporting on this is gonna need to be thought about as well. Because there will be errors to report on.
d
@Adam Cameron,
Still don't think stuff like 
select cf.listFirst(fullname, ' ' ) as firstname from users
is compelling. I think Brad's client might be trying to solve their perceived issue in a subpar way.
I think the useful comes more when trying to join values or filter on values. Or what if you're massaging the query for another process (like exporting to CSV/Excel/whatever). Yes, there are other ways you can approach it, but in some cases there's convience there and isn't the benefit of language like Lucee, having convience helpers? That said, I do agree it's a slipperly slope when it comes to support. Just because something can be added, doesn't always make it a good idea. The more things that are added, the more chance their are of bugs and there's more things to support. But I know the team working on Lucee is all to familiar with those concepts.
a
I think the useful comes more when trying to join values or filter on values. Or what if you're massaging the query for another process
I don't think "in a QoQ" is going to be the best place for either of those to happen, 99% of the time. TBH I don't think QoQ is the best place to do anything, 99% of the time 😉
d
@Adam Cameron And I agree with you on that. I avoid using it, but there are some use cases where it simplifies things greatly.
a
The one thing that it could be quite good at doing is stitching together two recordsets from disparate data sources. But that's the one thing it's never been able to do. For anything else it seems like a glorified 
evaluate()
call to me. I think if back when the concept of record sets were added to CFML we also had the collection-manipulation higher-order functions added in and were robust, then the thought of QoQ never ever would have come about. Obvs I know the history of when things were added to CFML and how it evolved, so I know that was never viable, and I am not indicting anyone. I'm just wittering on about how I think QoQ are a bit of an odd one to start with.
b
I don't think QoQ is the best place to do anything, 99% of the time
Micha, Gert, et al gave QoQ a bad name over the years (I've heard them disparage it several times at conferences) and I think it's rather unfortunate. It's a natural method of working with "sets" of data and a lot of people really like it. I can't help but think that much of the core Lucee teams dislike of QoQ stemmed far more from their specific implementation which performed VERY poorly and was full of bugs. It worth pointing out, that original implementation has been fully refactored, optimized, and improved. It's not fair for the concept to be sullied by the previous implementation quality, nor is someone's personal taste for the best way to solve a problem a very valid critique of whether an idea has merit. Otherwise, it's like me telling someone I think heated seats are a silly feature that shouldn't be installed in an automobile but then qualify it with the information that I don't actually have a drivers license and I don't even drive a car at all. I personally think QoQ is really one of the stand-out features of CFML and I've made Java developers jaws drop at how simple and easy basic manipulation of data can be done server-size using a familiar SQL syntax, which I still consider to be superior to the functional approach in many cases that require joining, aggregating, distincting, and unioning.
a
Agree with what you say re the Lucee Dev Team's perception of it being... what sounds like... a bit of a toy-throwing exercise more than an objective evaluation of the situation. I kinda hope you are not alluding to my negative opinion of its merits in your heated seats / lack of driver licence metaphor. I like to think my positions on things are more informed and well-articulated than that.
b
It's not a matter of being uninformed or or poorly articulated. I've just noticed that the people most resistant to QoQ feature additions are often times the same people who dislike QoQ in general, don't use it, and actively discourage anyone from using it. And while that doesn't prevent them from having valid critiques, it also means they're not the target market for these features and I have weigh how much of their pushback comes because the proposed enhancement on its own is ill conceived, and how much is just their general distaste for that manner of solving things using that language construct... which is sort of irrelevant 🙂 It seems every time any sort of QoQ discussion comes up, I have to field the usual bevy of comments from people who wish it weren't a part of the language at all, which may be a perfectly fine conversation to have somewhere else, but sort of off-topic.
Thus, my example of a car manufacturer discussing the features for their next year's model while the Amish over in the corner keep bringing up how they don't need cars. That's great for them (and I respect their choice), but fairly off topic for the rest of the car-using society 🙂
z
I think QoQ is great for making Lucee more friendly for data engineers for example, but we'd need better join support
the nice side effect of improving QoQ is we hit some other bugs which then help improve lucee overall
Open in Slack
PreviousNext
Powered by