A reader asks:
Is there any benefit to use the queryAddColumn/querySetCell functions instead of using just one SQL statement?
As far as I know - no. One of the general tips I would pass on to people is - if you ever find yourself doing post processing on a query, either in the output, or in the data itself, you should go back to your original SQL and see if you can change it there instead. Here are a few examples....
- You run a query to get the title and body from a table. When you output, however, you use left(body, 500) to create an abstract from the body. It would be better to do the left() in the original sql. This reduces the amount of data being transferred and the amount of work ColdFusion is doing. As a proud member of the Lazy Organization, less work is good(tm).
- You perform a query to get people's gender and age. When displaying this query, you do some logic to see if they are male and over 50, or female and over 45, and if so, you flag the row. This too can be done in the original SQL using a CASE statement I believe. (I always have to look up my reference guide for using CASE.)
The point is - if you aren't just dumping the query straight to output, you may want to look at the logic you are doing and see if it can be moved to SQL. Now - this is definitely one of those tips where I do not always practice what I preach, but it is a good tip to watch out for.
I'd say I probably only use querySetCell() when I'm building a test query by hand. That by itself is pretty useful. You don't always have a database available, so queryNew(), querySetCell(), and queryAddColumn() are a good way to create some quick sample data for testing purposes.
Archived Comments
Why handbuild a query? One reason is to create recordset for a unit test. Another use is in coding when you haven't built the database yet. The famed "querysim" function does this task very well. More info at http://www.cflib.org/udf.cf... and http://www.halhelms.com/ind...
I love case statements, and use them everywhere. Here are a few quickies for MS SQL 2000:
Lets say you want to get a total number of orders with a certain dollar value or higher, you can do a case inside of a sum like this:
SELECT SUM(CASE WHEN A.ORVAL > 500 THEN 1 ELSE 0 END) as RecordsOver500
You can also do sub-selects inside of a case statement, like this:
SELECT
CASE WHEN (SELECT SUM(ORVAL) FROM ORDER WHERE State = A.STATE) > 250 THEN A.STATE ELSE 'blank' END as StateList
I use SQL for logic far more than CF, as the results come back sooo fast (either against my MS SQL server, or my iSeries AS400), and loops are just too slow =)
I've used these functions to get around poor database design. You know the kind of tables with
"data1" "data2" "Data3" etc..
Business reasons dictated that the DB could not be changed, so this was the only solution I could come up with to get a total count of 'data', was to loop over the query, create an in-memory query using the query functions, then use query of query with aggregate functions to get the query.
Just yesterday I used these functions for a more 'respectable' purpose. I had a scheduling app where each user goes through a series of ~8 steps, in sequential order. The database only knows the steps they've gone through, not the ones they haven't. Until the appointment is schedule, an entry in the database doesn't exist yet. The database doesn't know what the next appointment is.
As such, we had written business logic to display a list of all users and their next appointment. It went something like this:
If currentappointment is 'x appointment' and currentappointment is 'completed' {
next appointment must be 'y appointment'
} else
display current appointment
}
Yesterday they requested a feature to sort the view by the next appointment. I took the "display code" business logic, and used it to hand create a query, which could then be sorted using query of query. I don't believe there would have been a way to write the business logic in straight SQL.
Just to be clear, Jeff - even if you can't change the db, you could use sql to rewrite those columns into nicer names. I think a slightly better example would be a case where not only the db can't change, but you can only use stored procedures written by a DBA.
Good post and some solid advice. Recently on a large project we were having some processing issues, and sure enough, all related to post processing of the queries. Some rewriting of the initial SQL took processing time for 30 seconds to under 3.
Good topic Ray, but not too long ago I noticed a QuerySetCell in your blog.cfc, specifically for your entries query and your comments query. So, what is the purpose in that situation? (sorry if this is one of those, I don't practice what I preach).
JimJay - didn't you see what I said? Listen to what I say - not what I code. ;) Seriously - if I remember right, it was a case of not wanting to have super complex SQL since I support 3 database types in blogcfc. Again, if I remember right.
Ray, just to clarify; I didn't mean specifically that the names of the columns were poorly named.
I meant that data was stored, like this, in one table:
User(UserID, Data1, Data2, Data3, Data4)
Instead of like this:
User (UserID)
Data (DataID, UserID, Data)
Aliasing the column names would not have addressed that issue.
That makes a bit more sense. Thanks for the clarification Jeff.
I have long been a proponent of doing data processing on teh DB server...that is what it was designed for.
I may be wrong, but off the top of my head, when you have a query of queries, you can run an update or insert statement upon it, but you add columns, row, and set data using queryAddColumn, querySetCell, etc...
This is what I remember off the top of my head.
Sami
I'm sure Sami meant "can't" there, not "can", and yes, you are right. You can't insert/delete into a in-memory query.
Oops. Yes, its "can't". Thanks Ray.
Add we use this technique sometimes for reports. There is also a useful UDF at cflib called queryAddColumnWithValue which adds a column with a default value which works great as well.
Sami
Man, my spelling today is horrible. :)
queryAddColumn is useful for example code.
I can see where you might want to populate a dropdown list from a table and make the first entry be "Please make a selection", which of course is not in the table, but there's probably a way to do this with a view.