Query map(), allow mapping to add/remove columns on the query?

webonix · October 12, 2015, 6:53am

I have been discussing with @seancorfield the query map() member function.

Given an array of structs, I can map to an array of boolean. Should a query map function be able to return a query with different columns, as with previous example, a query with one column containing true or false.

If I had a query with ‘name’ and ‘data of birth’, should map() be able to return a query with those two columns plus ‘age’?

each() could most likely be used an solve these examples, but was just wonder if map() should work this way.

seancorfield · October 12, 2015, 7:14am

I wasn’t sure of the current behavior so I played around on trycf.com and was a bit surprised that if I started with a query containing columns a, b, c I could not return structs from my mapped function that contained fields x, y, z. Well, I could but they were just thrown away and I ended up with a query with columns a, b, c that were empty.

So I think either:

it should be an error to return different column values or
the columns returned by the function should determine the shape of the final query

The second approach raises the question of what should happen if the mapped function returns different columns on each row (I think the result query would contain ALL those columns but only have values for them in the rows that specified them).

adam_cameron · October 12, 2015, 7:45am

I think that’s the most sensible approach. Realistically, I’d say >50% of the time I would want to do a remapping sort of operation on a query, it would be to create additional derived columns. I think there’s also a fairly reasonable use case for deriving different columns depending on the analysis of the original columns.

dom_watson · October 12, 2015, 8:19am

I also think that this would be the most useful approach. Agree that the most likely scenario for myself using this would be to add derived columns

bdw429s · October 14, 2015, 5:58pm

This seems like an intuitive way to create computed columns. One question to answer is what value should be used for rows which do not specify a value for a given column. Given the precedent of CFML, I’d suggest an empty string, however there’s probably a good argument for null in LuceeLang.

adam_cameron · October 14, 2015, 8:17pm

Having a look at this code, there’s “less than ideal” behaviour on the part of both CF and Lucee:

github.com

adamcameron/cfml/blob/main/dataTypes/queries/columns/defaultValue.cfm

<cfscript>
q = queryNew("data");
q.addColumn("id", "integer", [1,2,3]);

writeOutput("#q.id[1]#: isNull(q.data[1]): #isNull(q.data[1])#<br>");
</cfscript>
<cfloop query="q">
	<cfoutput>#id#: isNull(data): #isNull(data)#<br></cfoutput>
</cfloop>

On CF:

1: isNull(q.data[1]): NO
1: isNull(data): YES
2: isNull(data): YES
3: isNull(data): YES

On Lucee:

1: isNull(q.data[1]): true
1: isNull(data): false
2: isNull(data): false
3: isNull(data): false

I think CF is closer to “the truth” here. There are no values in that data column, so it should be null. I’m OK with the null being coerced into an empty String in situations where a String is called for, but a null check in all these situations should return true.

I think there’s an issue with Lucee here in that at least the false results should be true. I am not sure if there’s benefit in returning true for that first test though, for the sake of CF compat? But that’s as an aside to the issue here.

Empty column values should be null.

If Lucee’s CFML implementation is happy to diverge from CF’s with new features (I don’t think it should now that you have “LuceeLang”, but I am in a minority here, I know), then Lucee’s free to set the precedent here, as CF has yet to implement .map(). Although I’m leading the charge with that particular issue for ColdFusion, and I will be recommending (for what my recommendations with Adobe are worth, which is close to nil ;^) null when the question inevitably arises. Irrespective of what Lucee decides to do.