I am migrating a CB app from DAO (queryexecute) to quick. I know ORM has an EntitytoQuery. Does quick have something similar? I was playing with changing some views to work with Entities using getters. I have a pagination form I changed from cfloop query to cfloop index and getters. Works. (Can’t use sql pagination due to my MS SQL version)
I then ran into a problem with a slowdown on one form that loads 250 users and paginates the page. It was taking 5 secs in preEvent to PostEvent. I changed the query to query.retrieveQuery().get() and that fixed it but I then had to change my view to use prc.query[i].field instead of prce.query[i].getField().
What is the correct way? Why was the former so slow? (I guess loading the whole entity?)
Small update with the slowdown. I notice that with retrieveQuery(), I get 1 User Table Entity. Without I get 250 Table Entities. I guess this is the slowdown. It is a basic “select * from Users where role=1” sql statement. No joins/with(). On both, it is 1 sql query in cbdebugger and completes under 100ms.
You are correct that Quick loads entities (CFCs) by default. If you are loading hundreds or thousands at once, you will notice a slowdown. It’s very possible in those cases you don’t need the entity, just the data (e.g. for picklist data in a <select> field). Using retrieveQuery() drops down to the qb level and returns structs instead of entities. This will drastically improve performance as you return many records. For one to a handful of records, this is usually negligible, and you may also want to full entity to interact with. For picklist data or reports, you just want the data as fast as possible.
In cbdebugger there is another count that shows “Entity Count”. That can be a sign that you are loading a collection as entities that may be better as structs.
Bummer. I noticed retrieveQuery() only returns the main table fields if you have a with(). I have a bunch of lists with joins.
Went back to the drawing board. I will use qb to make my list queries and quick when I need to edit a particular item. My old version had a DAO with functions that brought back queries with Query(). (ex. OrderDAO.cfc had functions for all my sql queries dealing with orders). I have now kept the DAO but changing the queries to qb and just return the base qb query. In the handler I then add any order(), get(), etc. This allows me to inherit functions in the DAO and add onto the qb query in the DAO to make a new function.
This app was from Coldfusion 6. I rewrote it to Coldbox. Right now I have everything displaying but no update/add code. With the DAO models, I was able to move all the sql statements out of individual cfm files. Now I can keep some as qb queries and replace a bunch with quick for the add/update.
.with() eager loads data in a second query. So it couldn’t really return data if you’re dropping down to retrieveQuery() because you’re only getting one query. It would be cool if, down the road, you could .retrieveEagerLoadQueries() to get multiple queries back, but that’s another question.
What you can do here is to use subselects, because those fields will stay as part of the base query. We do this a lot – anything we can add on as a subselect field to a Quick entity, you can easily grab in “raw” query form without dealing with the object overhead.