CODEWITHSUNDEEP
mysqlcoldfusion
HPWD
HPWD

Reputation: 2240

Output with Grouping and mySql Order By

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.

Upvotes: 0

Views: 154

Answers (1)

HPWD
HPWD

Reputation: 2240

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>

Upvotes: 1

Related Questions