[ColdBox 3.7.0] Multiple Criteria Counts Suggestions

Communities ColdBox HMVC
1

Hi Guys,

Just looking for any suggestions on how to improve this bit of code. I have an Alpha Numeric filter for an entity. So I have the buttons 123 A B C D E F … and so on. Click on a letter and the results are filtered by the the first character. I use this bit of code to count the number of items available for each letter. If they have zero items I disable the button which gives a nice visual representation of letters that have or have no content (attached screenshot).

`

rc.filterCounts = structNew();

rc.filterCounts.numeric = companyService.newCriteria().cache(true).sqlRestriction(“substring( company_name, 1, 1) < ‘A’”).count();
rc.filterCounts.a = companyService.newCriteria().cache(true).ilike(“companyName”, “A%”).count();
rc.filterCounts.b = companyService.newCriteria().cache(true).ilike(“companyName”, “B%”).count();
rc.filterCounts.c = companyService.newCriteria().cache(true).ilike(“companyName”, “C%”).count();
rc.filterCounts.d = companyService.newCriteria().cache(true).ilike(“companyName”, “D%”).count();
rc.filterCounts.e = companyService.newCriteria().cache(true).ilike(“companyName”, “E%”).count();
rc.filterCounts.f = companyService.newCriteria().cache(true).ilike(“companyName”, “F%”).count();
rc.filterCounts.g = companyService.newCriteria().cache(true).ilike(“companyName”, “G%”).count();
rc.filterCounts.h = companyService.newCriteria().cache(true).ilike(“companyName”, “H%”).count();
rc.filterCounts.i = companyService.newCriteria().cache(true).ilike(“companyName”, “I%”).count();
rc.filterCounts.j = companyService.newCriteria().cache(true).ilike(“companyName”, “J%”).count();
rc.filterCounts.k = companyService.newCriteria().cache(true).ilike(“companyName”, “K%”).count();
rc.filterCounts.l = companyService.newCriteria().cache(true).ilike(“companyName”, “L%”).count();
rc.filterCounts.m = companyService.newCriteria().cache(true).ilike(“companyName”, “M%”).count();
rc.filterCounts.n = companyService.newCriteria().cache(true).ilike(“companyName”, “N%”).count();
rc.filterCounts.o = companyService.newCriteria().cache(true).ilike(“companyName”, “O%”).count();
rc.filterCounts.p = companyService.newCriteria().cache(true).ilike(“companyName”, “P%”).count();
rc.filterCounts.q = companyService.newCriteria().cache(true).ilike(“companyName”, “Q%”).count();
rc.filterCounts.r = companyService.newCriteria().cache(true).ilike(“companyName”, “R%”).count();
rc.filterCounts.s = companyService.newCriteria().cache(true).ilike(“companyName”, “S%”).count();
rc.filterCounts.t = companyService.newCriteria().cache(true).ilike(“companyName”, “T%”).count();
rc.filterCounts.u = companyService.newCriteria().cache(true).ilike(“companyName”, “U%”).count();
rc.filterCounts.v = companyService.newCriteria().cache(true).ilike(“companyName”, “V%”).count();
rc.filterCounts.w = companyService.newCriteria().cache(true).ilike(“companyName”, “W%”).count();
rc.filterCounts.x = companyService.newCriteria().cache(true).ilike(“companyName”, “X%”).count();
rc.filterCounts.y = companyService.newCriteria().cache(true).ilike(“companyName”, “Y%”).count();
rc.filterCounts.z = companyService.newCriteria().cache(true).ilike(“companyName”, “Z%”).count();

`

Is there a better way of getting these counts?

Thanks,

Richard

2

Personally, I would suggest just doing a regular SQL query for this, especially if you can get it all in a single pivot query or something like that. Less queries, less code.

Criteria Builder is great for dynamically building queries, but sometimes leveraging cfquery will let you accomplish the same end goal with less headaches. As with everything else, when to use which depends on the context and need. Good luck!

3

Yeah that’s what I was thinking, though it’s got even more complicated now as I have added other filters into the mix. I will have a think.

Thanks,

Richard

4

Hmm, depending on how big the database is, I’d be tempted to push it all into one SQL statement. Select out the distinct count of substrings of the first character, then pivot the result set. If the column exists, then there’s at least one record for it. Seems like less DB chatter than all those separate queries. Of course, I’m more of a direct SQL guy so I don’t know right off how to do exactly that in a criteria query :slight_smile:

Thanks!

~Brad

ColdBox Platform Evangelist
Ortus Solutions, Corp

E-mail: brad@coldbox.org
ColdBox Platform: http://www.coldbox.org
Blog: http://www.codersrevolution.com