Skip to main content
L
Lee Crockett
Known Participant
December 28, 2009
Answered

Sorting, grouping multiple times

  • December 28, 2009
  • 1 reply
  • 742 views

I have to admit I have no idea where to start with this one.  I have a bunch of lesson plans in a database, they have various fields like this:

unitOrLesson

title

grade

subject

inProgess

I need to display them all on a page sorted in a few different ways.  One way would be by grade, then subject, then lesson or units, then in progress or completed.  So it would be:

GRADE LEVEL

     SUBJECT - Lesson plans

          Title of in progress lesson 1

          Title of in progress lesson 2

          Title of in completed lesson 1

          Title of in completed lesson 2

     SUBJECT - Unit plans

          Title of in progress Unit 1

          Title of in progress Unit 2

          Title of in completed Unit 1

          Title of in completed Unit 2

Another option would be by subject, then grade, then lesson or units, then in progress or completed. Like this:

SUBJECT

     GRADE - Lesson plans

          Title of in progress lesson 1

          Title of in progress lesson 2

          Title of in completed lesson 1

          Title of in completed lesson 2

     GRADE - Unit plans

          Title of in progress Unit 1

          Title of in progress Unit 2

          Title of in completed Unit 1

          Title of in completed Unit 2

I started writing a the query below, and then realized I had absolutely no idea where to go or how to make this happen:

SELECT kits.kitsID,

kits.unitLesson,

kits.status,

kits.title,

grade.level,

subjects.subjectName,

grade.gradeID

FROM kits INNER JOIN grade ON kits.grade = grade.gradeID

INNER JOIN subjects ON kits.subject1ID = subjects.subjectsID

ORDER BY grade.gradeID ASC, subjects.subjectName ASC

Of course the above doesn't work. I just don't know how best to get this out of the database and onto the page.  Any help would be greatly appreciated.

This topic has been closed for replies.
Correct answer Dan_Bracuk

There is a simpler and faster way.  First, you only need one trip to your db, so the queries rsGrades and rsSubjects are uncessary.  You got the data from the 1st query.

Next, cfoutput has a group attribute and there is no indication you attempted to use it.  When you do use it, you have an order by clause in your query.  In your case, since you are grouping more than once, you have to order by more than one field.  In other words, if you are going to group by field1, field2, and field3, in that order, you have to sort your query by those same three fields in the same order.

google "cfoutput" to bring up the adobe reference.  The query section will show an example of the group attribute.

1 reply

L
Lee CrockettAuthor
Known Participant
December 28, 2009

I've been working on this for a while. I'm working on looping over query of queries. Can someone tell me if I'm on the right track?

<cfquery name="rsKits" datasource="fluency">

SELECT kits.kitsID,

kits.unitLesson,

kits.status,

kits.title,

    grade.gradeID as grade,

grade.level,

subjects.subjectName,

grade.gradeID

FROM kits INNER JOIN grade ON kits.grade = grade.gradeID

INNER JOIN subjects ON kits.subject1ID = subjects.subjectsID

order BY grade.gradeID ASC, subjects.subjectName ASC

</cfquery>

<cfdump var="#rsKits#">

<cfquery name="rsGrades" datasource="fluency">

SELECT *

FROM grade

</cfquery>

<cfloop query="rsGrades">

    <cfquery name="bygrade" dbtype="query">

     select * from rsKits

     where grade = #gradeID#

    </cfquery>

    <cfoutput>Grade Level: #gradeID#</cfoutput><br/>

<cfloop query="bygrade">

<cfoutput>#bygrade.title#</cfoutput><br />

    </cfloop>

    </cfloop>

L
Lee CrockettAuthor
Known Participant
December 28, 2009

I guess if you work at it long enough, you find a solution.  Here is what I have before formatting and clean up.  It works, but if there is a faster or easier way, I'd love to hear it.

<cfquery name="rsKits" datasource="fluency">

SELECT kits.kitsID,

kits.unitLesson,

kits.status as status,

kits.title,

    grade.gradeID as grade,

grade.level,

    subjects.subjectsID as subID,

subjects.subjectName as subName,

grade.gradeID

FROM kits INNER JOIN grade ON kits.grade = grade.gradeID

INNER JOIN subjects ON kits.subject1ID = subjects.subjectsID

</cfquery>

<cfdump var="#rsKits#">

<cfquery name="rsGrades" datasource="fluency">

SELECT *

FROM grade

</cfquery>

<cfquery name="rsSubjects" datasource="fluency">

SELECT *

FROM subjects

</cfquery>

<cfloop query="rsGrades">

    <cfquery name="bygrade" dbtype="query">

        SELECT * from rsKits

        WHERE grade = #rsGrades.gradeID#

    </cfquery>

    <cfloop query="rsSubjects">

        <cfquery name="subsbygrade" dbtype="query">

            SELECT * from bygrade

            WHERE subID = #subjectsID#

        </cfquery>

        <cfquery name="unitsSubGrade" dbtype="query">

        SELECT * from subsbygrade

            WHERE unitLesson = 'u'

            ORDER BY status ASC

        </cfquery>

        <br /><cfoutput>#rsGrades.level#</cfoutput><br/>

        <cfoutput><span style="margin-left:10px;">#rsSubjects.subjectName# - Units</span></cfoutput><br/>

        <cfoutput query="unitsSubGrade">

<span style="margin-left:20px;">#title#</span><br />

        </cfoutput>

        <cfquery name="lessonsSubGrade" dbtype="query">

        SELECT * from subsbygrade

            WHERE unitLesson = 'l'

            ORDER BY status ASC

        </cfquery>

        <br /><cfoutput>#rsGrades.level#</cfoutput><br/>

        <cfoutput><span style="margin-left:10px;">#rsSubjects.subjectName# - Lessons</span></cfoutput><br/>

        <cfoutput query="lessonsSubGrade">

<span style="margin-left:20px;">#title#</span><br />

        </cfoutput>

    </cfloop>

</cfloop>

D
Dan_BracukCorrect answer
Inspiring
December 28, 2009

There is a simpler and faster way.  First, you only need one trip to your db, so the queries rsGrades and rsSubjects are uncessary.  You got the data from the 1st query.

Next, cfoutput has a group attribute and there is no indication you attempted to use it.  When you do use it, you have an order by clause in your query.  In your case, since you are grouping more than once, you have to order by more than one field.  In other words, if you are going to group by field1, field2, and field3, in that order, you have to sort your query by those same three fields in the same order.

google "cfoutput" to bring up the adobe reference.  The query section will show an example of the group attribute.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices