Output with Grouping and mySql Order By

Question

For demo purposes, let's say I have the following query:

SELECT a_label, aID, b_label, bID, c_label, cID, d_label, dID, orderByA, orderByB, orderByC 
FROM table
ORDER BY orderByA, orderByB, orderByC

On the display side, the LI can be sorted and their corresponding positions are stored in the corresponding tablecolumn.

On my output, I'm looping over the data and need to use the <cfoutput query="qryName" group="aID"> and then a subgroup is using <cfoutput group="bID"> with another sub group using <cfoutput group="cID"> and so on.

My problem is I can't figure out the combination of what the query should look like so that my output is grouped correctly. Do I need to group in my sql query or leave it in the cfm code? And then what about the order by?

I guess what I am asking is how do you know when the grouping belongs in the query vs the code?

UPDATED WITH SOME CFM CODE

<cfoutput query="#aw#" group="cID">
    <ul class="listItem pItem">
        <cfoutput group="pID">
            <li class="listItem pTitle"  data-cid="#aw.cID#" data-pid="#aw.pID#" data-wid="#aw.wID#">
                <i class="glyphicon glyphicon-chevron-right rotate wToggle"></i> <input type="checkbox" name="p" class=""> #aw.pLabel#
                <ul class="listItem wItem hide">
                    <cfoutput group="wID">
                        <li class="listItem wTitle" id="w_#aw.cID#_#aw.pID#_#aw.wid#_#aw.woaid#">
                            <i class="glyphicon glyphicon-chevron-right rotate dToggle font-blue-madison"></i> <input type="checkbox" name="w" class=""> #aw.wLabel#
                            <ul class="listItem dItem hide">
                                <cfoutput>
                                   <li class="listItem dTitle" id="d_#aw.cID#_#aw.pID#_#aw.wID#_#aw.dID#_#aw.woaid#"> 
                                        <input type="checkbox" name="d" class="child"> #aw.dID#
                                    </li>
                                </cfoutput>
                            </ul>
                        </li>
                    </cfoutput>
                </ul>
            </li>
        </cfoutput>
    </ul>
</cfoutput>

The user can drag items from one section to another section but only if the sections are the same level. For example, a dItem can only be moved to another container that has a dItem. Likewise, a wItem can only be moved to another wItem and so on.

I'm working on the sql query and will update this question. I'm starting at one level at a time instead of the entire query at the same time (making this bite-sized).

UPDATE I think I found the problem, bad data. Some of the items do not have a value in their respective orderBy* so the grouped items aren't in the right order within a subgroup. The default value for newly added items was NULL but I think that needs to be a different value. Is there a best-practice default value to use for sortby columns?

UPDATE ii I found this SO question, Insert and set value with max()+1 problems, which I could implement for when new items are added. That way it would appear at the end of the list.

Grouping in query should be when you're trying to do manipulation of the data that requires grouping, like aggregation. Grouping in the code should be when you're trying to separate out how you want to display those results. If you are trying to do aggregation in code, you might be better off doing it in your query. — Shawn, Jan 14 '19 at 03:27
So as long as the sql query is producing the results I need, then I’m good there. Then it’s just a matter of getting the display to display the content correctly. Just to confirm, if I need to group in my view, it doesn’t necessarily mean my query needs to be tweaked. Correct? — HPWD, Jan 14 '19 at 03:36
Can you provide an example of what you mean by "grouped correctly"? That'll clarify whether the grouping should be done in CF or SQL. — SOS, Jan 14 '19 at 04:31
You might be overthinking this. If you run the code you described, does it give you what you want? — Dan Bracuk, Jan 14 '19 at 04:56
I am of the belief that a SQL query should give you the data you're looking to get and not much more. If you need to display it, then you handle that with your in-code View. It is sometimes just as easy to structure your query in such a way that it assists your View, but your call to your database shouldn't be overly concerned with how you'll output your data. Your next developer (or You+6 months) might want to display that data some other way. But as the others said, can you provide an example of what you have and what you want? As with all things in tech, my advice falls under "It depends". — Shawn, Jan 14 '19 at 05:35
What does the above code do now - and what should it do instead? Seeing a dummy example of the expected vs desired output would help. — SOS, Jan 14 '19 at 21:29
@Ageax I just updated the question - I may have some bad data throwing this off. Dan Bracuk may be correct in me overthinking this. To answer your question though, the items in the sub groups are not in the order I am expecting thus why I thought my sql query was wrong. When I assign some values to the `orderBy*` columns, the output works as expected, I think. Further testing is needed but I really think bad data is the problem. — HPWD, Jan 14 '19 at 21:34
Okay, that's what I was asking about sample data. The sql sounded okay. A small data sample often makes things like that more obvious to those of us that can't see the actual output ;-) — SOS, Jan 14 '19 at 21:44
Next time, and I'm sure there will be a next time, I'll remember to include data, too. :) Thanks for staying with me on this issue. Do you have any advice for "The default value for newly added items was NULL but I think that needs to be a different value. Is there a best-practice default value to use for sortby columns"? — HPWD, Jan 14 '19 at 22:15
There's other possibilities, like using a CASE to add a dynamic sort column, ie ORDER BY CASE WHEN COLA is NULL THEN 2 ELSE 1 END, COLA .. or using the max value for the datatype, i.e. COALESCE(ColA, {MaxValueForDataType} — SOS, Jan 15 '19 at 12:53
Is {MaxValueForDataType} a function or simply a placeholder for an additional function/query lookup? I toogled it and din't find any results for 'MaxValueForDataType'. The RDBMS is Maria. — HPWD, Jan 15 '19 at 19:13
Didn't see your comment without the "@". It is just a placeholder for a hardcoded value representing the max number for the column datatype. — SOS, Jan 18 '19 at 22:41

score 1 · Accepted Answer · answered Jan 18 '19 at 21:52

Got it! My <cfoutput...group=... need to match/group by the same columns (and order) as the in the GROUP BY clause. Once I made them match, my output matched what I was seeing the database.

Updated SQL
SELECT a_label, aID, b_label, bID, c_label, cID, d_label, dID, orderByA, orderByB, orderByC FROM table ORDER BY cID, orderByA, orderByB, orderByC

orderByC is just for good measure, there is no grouping needed for the last <cfoutput> tag.

Updated CFM (removed the non-essential code to highlight the solution

<cfoutput query="#aw#" group="cID">
<ul class="listItem pItem">
    <cfoutput group="orderByA">
        <li class="listItem pTitle">
            <i class="glyphicon glyphicon-chevron-right"></i> #aw.pLabel#
            <ul class="listItem wItem hide">
                <cfoutput group="orderByB">
                    <li class="listItem wTitle">
                        <i class="glyphicon glyphicon-chevron-right"></i>#aw.wLabel#
                        <ul class="listItem dItem hide">
                            <cfoutput>
                               <li class="listItem dTitle"> 
                                    #aw.dID#
                                </li>
                            </cfoutput>
                        </ul>
                    </li>
                </cfoutput>
            </ul>
        </li>
    </cfoutput>
</ul>

Ohh.. yes. Totally missed that your output columns were ordered differently. Wish the documentation emphasized that requirement more. — SOS, Jan 18 '19 at 22:14
Me too. Would have saved me hours of trying the various combinations of this order by... live and learn. — HPWD, Jan 18 '19 at 23:12

Output with Grouping and mySql Order By

1 Answers1