Highlighted

Sorting by database field when using cfdirectory

Contributor ,
Sep 04, 2015

Copy link to clipboard

Copied

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>

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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>

Views

422

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Sorting by database field when using cfdirectory

Contributor ,
Sep 04, 2015

Copy link to clipboard

Copied

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>

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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>

Views

423

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Sep 04, 2015 0
Adobe Community Professional ,
Sep 05, 2015

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 05, 2015 0
Contributor ,
Sep 08, 2015

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 08, 2015 0
Contributor ,
Sep 21, 2015

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 21, 2015 0
Adobe Community Professional ,
Sep 21, 2015

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 21, 2015 0
Contributor ,
Sep 22, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 22, 2015 0
Adobe Community Professional ,
Sep 22, 2015

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 22, 2015 0
Contributor ,
Sep 22, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 22, 2015 0
Adobe Community Professional ,
Sep 22, 2015

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 22, 2015 0
Contributor ,
Sep 23, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 23, 2015 0
BKBK LATEST
Adobe Community Professional ,
Sep 23, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 23, 2015 0