Sorting by database field when using cfdirectory

[jlig]

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>

[BKBK · Accepted solution]

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 = 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>

[jlig]

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

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.

[jlig]

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?

[BKBK]

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.

[jlig]

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:

[BKBK]

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.

[jlig]

Thanks BKBK..

Here is the actual table view:

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".

[BKBK]

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>

[jlig]

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:

[BKBK]

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