CfWheels: Subqueries Solution

Question

I am using CFWheels and trying to find a way to do subqueries. I want to know what options are available. Currently I am constructing my queries using plain old Coldfusion query and adding a huge string of the query in execution. I want to know if there are more cleaner options available using CFWheels model functionality.

My table is:

I wish to perform this query on the table:

select COUNT(tb1.formid) as number_of_submission, tb1.formid
from ( select formid, room, inspector
from test_tbl
group by formid, room, inspector) tb1
group by tb1.formid;

Which displays the following:

And just to elaborate a bit more, the inner query gives:

Would `select formid, count(distinct formid) number_of_submissions from test_tbl group by formid` not give you the information you need? — Dan Bracuk, May 11 '16 at 17:01
no it didn't work, mainly because if you group by formid, then there are only 2 form id there, what distinguishes each submission is a unique set of unique formid, room, and inspector. — Saad A, May 12 '16 at 15:22

score 0 · Answer 1 · answered May 13 '16 at 14:31

There is the grouping function available for models that looks something like this:

counts=model("test_tbl").findAll( 
     select="tb1.formid, COUNT(tb1.formid) AS publishCount"
    , group="formid, room, inspector"
);

but unless you can find a way to do a single group to get the result, you won't be able to use the model this way.

There are some other options too though, which might be viewed as less favorable.

use a (db) view - I've got several views going that have a this kind of grouping, and I use the models for read-only interaction on these. The downside is that some of the logic is in the views, and making changes is a little more cumbersome as a result.

use select, but as a function inside the model - add your select function inside the model and make it available through a function. When you want to access this kind of grouping or count, you would call the model like so test_tbl.getSubmissions(). This way your select code is in the model where it IMO should be as opposed to a controller.

Anecdotally... a while back I've gone through a bunch of models and replaced the complex multi table relationships with select for a significant performance boost. So using select is not necessarily a bad thing.

CfWheels: Subqueries Solution

1 Answers1