I love the QB query builder module. In its own description its touted as heavily inspired by Eloquent from Laravel, and it’s used to quickly scaffold simple queries, make complex out-of-order queries possible, and abstract away differences between database engines.

While it’s great in that it builds the actual SQL to run behind the scenes and executes it as the last step, hiding the complexity of individual SQL calls, it’s not easy to see exactly what’s running against the database. Having found myself needing to see the actual SQL queries, I figured out how to intercept the query execution and display everything that QB holds.

According to the documentation, there are two interception points: preQBExecute and postQBExecute, and the following information is available in the interceptData struct:

Name Type Description
sql String The sql string to execute
bindings Struct The struct of bindings (keys and values) for the query
options Struct Any options to pass along to queryExecute

The first step is to create the interceptor itself, making use of the preQBExecute interception point. Since I use ColdBox as my HMVC framework, I add this file in the /interceptors folder of a typical advanced ColdBox template.

Then I need to register the interceptor and allow it to fire. To do that I add a new configuration struct in the interceptors array of the /config/Coldbox.cfc configure() object.

Depending on my settings, I may need to reinit my application (usually append a fwreinit=1 to a ColdBox URL), and then I can see a typical dump of the entire QB interceptor stack.

qb Interceptor interceptData stack dump

It may be useful to note that if I use the postQBExecute() interception point, which looks the same as the preQBExecute(), then the returned result adds a result key and a query key containing the resulting recordset.

qb Interceptor postQBExecute interceptData stack dump

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.