10 Replies Latest reply on Sep 23, 2015 11:12 AM by BKBK

    Sorting by database field when using cfdirectory

    jlig Level 1

      I have the following working code but need to sort the list by a field (sortID) from my database (not a value from the directory)

      - How do I add this?

      - I tried adding "sortID" in below, but it doesn't have any effect?

      - sortID is a 3 digit varchar field in MySQL

       

      <div data-role="collapsible" data-collapsed="true">
          <h3 align="center">Board</h3>
          <ul data-role="listview" data-theme="d" data-inset="true">
            <cfset DirList = "C:\Inetpub\wwwroot\CRM\CCC\attachments\board\">
            <cfset site = ("https://crm.domain.com/crm/ccc/attachments/board/")>
            <cfdirectory directory="#DirList#" name="qDir" action="list" sort="sortID DESC">
            <cfquery dbtype="query" name="dirsOtherOnly">
        SELECT * FROM qDir ORDER BY sortID DESC
        </cfquery>
            <cfoutput>
              <cfloop query="dirsOtherOnly">
                <li>
                <a href="#site##dirsOtherOnly.name#" title="Click to Preview" target="_blank">#dirsOtherOnly.Name#</a>
              </li></cfloop>
           
            </cfoutput>
          </ul>
          <cfif ("sicr" NEQ "region6" AND #Session.kt_login_user# EQ "john" OR #Session.kt_login_user# EQ "bob")>
            <p align="left">Click to add attachment: <cfoutput><a href="ul_l.cfm?id2=500&id5=Board" data-transition="fade" target="_blank">Attachments</a></cfoutput></p>
          </cfif>
        </div>
      
      
      
        • 1. Re: Sorting by database field when using cfdirectory
          BKBK Adobe Community Professional & MVP

          Why would you want to order the result-set by a column that apparently has nothing to do with it? In any case, one solution is to get the sortID column from the database, and append it to the quey resulting from cfdirectory

           

          Something along these lines

           

          <cfdirectory directory="#DirList#" name="qDir" action="list">

           

          <!--- Get sortID from database --->

          <cfquery name="getSortID" datasource="yourDSN">

              SELECT sortID

              FROM TBL

          </cfquery>

           

          <!--- Create sortID array. --->

          <cfset sortIdArray = ArrayNew(1)>

          <cfset i = 1>

          <cfoutput query="getSortID">

              <cfset sortIdArray[i] = sortId>

              <cfset i = i+1>

          </cfloop>

           

          <!--- Use the array to add the sortID column to the qDir query. --->

          <cfset nColNumber = QueryAddColumn(qDir, "sortID", "VarChar",sortIdArray)>

           

          <!--- SortID is now a column of qDir. You may therefore use it in the order-by clause --->

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

          SELECT * FROM qDir

          ORDER BY sortID DESC 

          </cfquery>

          • 2. Re: Sorting by database field when using cfdirectory
            jlig Level 1

            BKBK, thanks for the help on this.
            Your code worked great except that I had to change the cfoutput to cfloop:

            2015-09-08_0956.png

             

            ps: I'm storing the document details in a database, then displaying in a jquery mobile list, and needed the files sorted by sortID.
            I allow the end user to change the sortID value as needed to sort the file list.

             

            Thanks again.

            • 3. Re: Sorting by database field when using cfdirectory
              jlig Level 1

              Quick question: the sort is correct the first time the page loads, but after a new doc is uploaded, the sort is out of whack? Any ideas? Is this a caching issue?

              • 4. Re: Sorting by database field when using cfdirectory
                BKBK Adobe Community Professional & MVP

                I would presume that the latest uploaded document is stored as the last row in the database. In that case, you have to again run through the process of fetching the data and sorting.

                 

                You could rewrite this entire process as a function. You would then reuse it each time, without any changes.

                • 5. Re: Sorting by database field when using cfdirectory
                  jlig Level 1

                  One thing I failed to mention.. I currently have this code block listed four times on the same page (to display different files/list)

                  Do I need to use unique naming for any of this code block, such as query names, qdir, etc?

                   

                  Also, upon further testing, see image below:

                  2015-09-22_0905.png

                  • 6. Re: Sorting by database field when using cfdirectory
                    BKBK Adobe Community Professional & MVP

                    It's clear what is happening and why. The remarks you mark in red imply that sortID is related to the Booard Update date. However, you specify that relationship nowhere.

                     

                    A good place to do so is in the query, getSortID. I am assuming that the table, tblAttachments, has a column relating to Board Update date.

                    • 7. Re: Sorting by database field when using cfdirectory
                      jlig Level 1

                      Thanks BKBK..

                      Here is the actual table view:

                      2015-09-22_1240.png

                      Using this table of data, I'm trying to get the list to do the following:

                      • Where ticketID = 500 (only want the "board" records)
                      • Order by sortID DESC (this sort value is manually set by the end-user when documents are uploaded)
                      • Display only the "document" name listing on the html page (always sorted by the "hidden" sortID column)

                       

                      I don't need (or want) to reference the date "modified" and "document" name fields, only thing of importance is that they display/list on the page based on "sortID".

                      • 8. Re: Sorting by database field when using cfdirectory
                        BKBK Adobe Community Professional & MVP

                        The solution is all there in my last post. You could, for example, use a join on the 'name'/'document' column (I am assuming the values in each row correspond). Then there is no need for the addition of a column.

                         

                        Something like this:

                         

                        <cfquery name="getSortID" datasource="cbi">

                            SELECT sortID, document as name

                            FROM tblAttachments

                            WHERE ticketID = 500

                        </cfquery>

                         

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

                        SELECT getSortID.*, qDir.datelastmodified, qDir.directory, qDir.size, qDir.type

                        FROM qDir, getSortID

                        WHERE getSortID.name = qDir.name

                        ORDER BY getSortID.sortID DESC

                        </cfquery>

                        • 9. Re: Sorting by database field when using cfdirectory
                          jlig Level 1

                          BK, that fixes the issue & I appreciate your help. I was working on the exact same query before you posted as the "name" field was the common link.

                          For the sake of future reviewers with a similar need, here is my final working code:

                          2015-09-23_1008.png

                          • 10. Re: Sorting by database field when using cfdirectory
                            BKBK Adobe Community Professional & MVP

                            In this approach, there is no longer any need to use the array.