I’d like some feedback, if you don’t mind.
We’re reworking our online store, and I’m looking at refactoring the ordering process. Right now, it uses a traditional approach. It inserts the order header, then loops over the items in the shopping cart and inserts them into the detail table. Like the following pseudocode:
<cftransaction> <cfquery datasource="#Request.DSN#"> INSERT INTO OrderHeader (...) VALUES (<cfqueryparam...>, ...) </cfquery> <cfloop from="1" to="#arrayLen(Session.Items)#" index="i"> <cfquery datasource="#Request.DSN#"> INSERT INTO OrderDetail (...) VALUES (<cfqueryparam...>, ...) </cfquery> </cfloop> </cftransaction>
Traditional, right? I want to refactor it into something like this:
<cftransaction> <cfquery datasource="#Request.DSN#"> INSERT INTO OrderHeader (...) VALUES (<cfqueryparam...>, ...) </cfquery> <cfquery datasource="#Request.DSN#"> INSERT INTO OrderDetail (...) SELECT p.SKU, p.Price, d.Qty, ... FROM ( <cfloop from="1" to="#arrayLen(Session.Items)#" index="i"> <cfif i GT 1>UNION ALL</cfif> SELECT <cfqueryparam value="#Session.Items[i].SKU#"...> AS SKU, ... </cfloop> ) AS d INNER JOIN Products AS p ON (d.SKU = p.SKU) </cfquery> </cftransaction>
Benefits:
- Instead of n+1 queries for each order there are exactly 2.
- It eliminates the need to do queries to look up prices or other information.
- In my tests it is faster, especially for large numbers of items.
The drawback of course being that it’s pretty nontraditional. I’d even venture to say that it’s counter-intuitive enough that most CF coders looking at it are going to scratch their heads and think I was on crack cocaine.
Thoughts?