CFWheels findAll Group By Having Clause

Question

In CFWheels I am looking at the findAll() page and it has a group by option by doesn't have a Having Clause option. Is there a way to use having clause using findAll() in CFWheels.

Answer 1

Just to share. Credit to Pankaj in the comment for his answer. Thank you

checklist = model("user_checklist").findAll(select="MAX(user_checklist.r_id)", group="r_id HAVING MAX(user_checklist.r_id) > 13");

gives you

SELECT MAX(user_checklist.r_id) FROM user_checklist GROUP BY r_id HAVING MAX(user_checklist.r_id) > 13

Answer 2

It is very disappointing that the dynamic filtering of the grouped data is not available even in the new release CFWheels 1.4.2.

What I found is this issue, it is a very old issue posted under the cfwheels google group and a google group discussion. Even to this day the workaround for using a having in the group by statement is to use the cfrel by dumphreys which is a ColdFusion Relational Algebra Framework.

I would recommend trying it, it is very easy to use and cleanly written. If you navigate to cfrel.cfc you would find a findAll() function which looks similar to the original findAll() in cfwheels (check out the \wheels\model\read.cfm), but there you'll find it supports having() right out of the box.

Example (cfrel having clause):

    /*  
    SQL: SELECT productId, SUM(total) AS totalSum FROM orders
         GROUP BY productId HAVING SUM(total) > ?
         ORDER BY totalSum DESC LIMIT 5
    PARAMS: [1000]
    */
    myOrdersRel = relation(datasource="cfrel")
        .select("productId,SUM(total) AS totalSum")
        .from("orders")
        .group("productId")
        .having("SUM(total) > ?", [1000])
        .order("totalSum DESC")
        .limit(5);
    query2 = rel2.query();