ValueList is not giving the right ORDER

Hi all. I have a listing of activities. After the normal queries I add the dates to a valueList to get a certain format in the <cfoutput>:


<cfquery name="getListing" datasource="#Application.dsn#">
      	SELECT
        		CL.category_id
					,	CL.category_icon
					,	CL.category_sequence
          ,	CL.category_isActive                  
        	,	C.language_abbr
          ,	C.category_name
          ,	CH.photo
          ,	AL.activity_id
          ,	A.language_abbr
          ,	A.activity_name
          ,	A.activity_title
          ,	A.activity_description
          ,	DATE_FORMAT( activity_date, '%d/%m') AS newDate
          ,	AD.activity_date
        FROM
        		category_list CL
				INNER
        	JOIN categories C
          	ON CL.category_id = C.category_id
           AND C.language_abbr = <cfqueryparam cfsqltype="cf_sql_char" value="#Trim( Session.language )#" />    
				LEFT
        	JOIN category_headers CH
          	ON CL.category_id = CH.category_id 
				INNER
        	JOIN activity_list AL
          	ON CL.category_id = AL.category_id 
				INNER
        	JOIN activities A
          	ON AL.activity_id = A.activity_id
            AND A.language_abbr = <cfqueryparam cfsqltype="cf_sql_char" value="#Trim( Session.language )#" />             
  			INNER
  				JOIN 	activity_dates AD
    				ON	AL.activity_id = AD.activity_id	
             
        ORDER
        	BY category_sequence, activity_name,  MONTH(AD.activity_date), DAY(AD.activity_date)                                            
      </cfquery>
      <cfquery name="getDates" dbtype="query">
      	SELECT
        	DISTINCT newDate
        FROM
        	getListing
      </cfquery>
			<cfset result = valueList ( getDates.newDate, ", " )>

But somehow the <cfoutput> keeps giving me the ordering by DAY instead of first MONTH and than DAY. What am I doing wrong?

add an ORDER BY clause to your getDates query

note: this ORDER BY clause will have to break apart the values in the newDate column, so that you can distinguish between the day (which comes first) and the month (which comes after the slash)

it would be far simpler ~not~ to build the newDate values as ‘%d/%m’ in the first place, yeah?

Hi Rudy I’m struggling with this for some time now. How would you do it?

depends on what you’re referring to

i would first need an explanation of what the two different queries do for you, and how you want the results to show on the web page

Hi Rudy with the first query I grab all activities from database in the second one I use the DISTINCT function for the , because If I don’t use that somehow I get the dates multiple times (depending on the number of activities), In other words, when there are three different activities, without using the second query, I get the dates for each activity 3 times.

your second query is fine, you’re using query-of-query correctly

Hi Rudy Like i said in the beginning of this post the way I have it is giving me the right output indeed, regarding to the dates. The only thing is it doesn’t give them in the right order

please bear with me and show the query again that produces the results that aren’t in the right order

Hi Rudy. This are the two queries.


<cfquery name="getListing" datasource="#Application.dsn#">
  SELECT
         CL.category_id
    ,     CL.category_icon
    ,     CL.category_sequence
    ,     CL.category_isActive                  
    ,     C.language_abbr
    ,     C.category_name
    ,     CH.photo
    ,     AL.activity_id
    ,     A.language_abbr
    ,     A.activity_name
    ,     A.activity_title
    ,     A.activity_description
    ,     DATE_FORMAT( activity_date, '%d/%m') AS newDate
    ,     AD.activity_date
  FROM
      category_list CL
  INNER
    JOIN categories C
      ON CL.category_id = C.category_id
     AND C.language_abbr = <cfqueryparam cfsqltype="cf_sql_char" value="#Trim( Session.language )#" />    
  LEFT
    JOIN category_headers CH
      ON CL.category_id = CH.category_id
  INNER
    JOIN activity_list AL
      ON CL.category_id = AL.category_id
  INNER
    JOIN activities A
      ON AL.activity_id = A.activity_id
  AND    A.language_abbr = <cfqueryparam cfsqltype="cf_sql_char" value="#Trim( Session.language )#" />            
  INNER
    JOIN      activity_dates AD
      ON     AL.activity_id = AD.activity_id                 
  ORDER
      BY category_sequence, activity_name,  MONTH(AD.activity_date), DAY(AD.activity_date)                                            
</cfquery>
<cfquery name="getDates" dbtype="query">
  SELECT
      DISTINCT newDate
  FROM
               getListing
</cfquery>
<cfset result = valueList ( getDates.newDate, ", " )>

The first one gets all the activities and the query of queries makes surre that not all dates are repeated over and over again depending on how many activities there are. But aas you can see on this page does it order on Day rather than on Month and than Day. While I have the order By that way in the first query. I also tried to add the ORDER BY in the query of quiries but without success.

This is driving me insane, because on the activity details page (see here) its working fine and I use the same method, but that is because (I guess) there is an activity_id involved. What should I do to get the right output.

Or should I do the formating in CF instead of in the Query. Any advise is more then welcome

your query-of-query with the DISTINCT is missing an ORDER BY clause, but the problem is, even if you had an ORDER BY clause, it wouldn’t work properly (in fact, DISTINCT uses an implicit sort)

if you generate %d/%m formatted dates, you cannot expect to sort on them meaningfully

so yeah, do the formatting in the application

you already have MONTH(AD.activity_date) and DAY(AD.activity_date) in the first query, so you should add them to the SELECT clause and then use them in the query-of-query

Rudy should I add them use them like that in bot queries. Can you please give an example of how that would look like? Thank you

this is pretty straightforward, old friend, you could’ve had it tested and working by now…

<cfquery name="getListing" datasource="#Application.dsn#">
  SELECT CL.category_id
       , CL.category_icon
       , CL.category_sequence
       , CL.category_isActive                  
       , C.language_abbr
       , C.category_name
       , CH.photo
       , AL.activity_id
       , A.language_abbr
       , A.activity_name
       , A.activity_title
       , A.activity_description
       , [COLOR="#0000FF"]YEAR(AD.activity_date)  AS activity_year
       , MONTH(AD.activity_date) AS activity_month
       , DAY(AD.activity_date)   AS activity_day[/COLOR]     
     FROM category_list CL
  INNER
    JOIN categories C
      ON C.category_id = Cl.category_id
     AND C.language_abbr = <cfqueryparam cfsqltype="cf_sql_char" value="#Trim( Session.language )#" />    
  LEFT outer
    JOIN category_headers CH
      ON CH.category_id = CL.category_id
  INNER
    JOIN activity_list AL
      ON AL.category_id = CL.category_id
  INNER
    JOIN activities A
      ON A.activity_id = AL.activity_id
     AND A.language_abbr = <cfqueryparam cfsqltype="cf_sql_char" value="#Trim( Session.language )#" />            
  INNER
    JOIN activity_dates AD
      ON AD.activity_id = AL.activity_id                 
  ORDER
      BY CL.category_sequence
       , A.activity_name
</cfquery>

<cfquery name="getDates" dbtype="query">
  SELECT DISTINCT 
         [COLOR="#0000FF"]activity_day || '/' || activity_month AS activity_ddmm[/COLOR]
    FROM getListing
  [COLOR="#0000FF"]ORDER
      BY activity_month , activity_day[/COLOR]
</cfquery>

<cfset result = valueList ( [COLOR="#0000FF"]getDates.activity_ddmm[/COLOR], ", " )>

:slight_smile:

I wish it was that straightforward to me Rudy :frowning: I see things I’ve never seen before. Anyway, I get a runtime error in the Query of Queries:

Cannot mix types VARCHAR and INTEGER in a + binary operation

SOLVED

CAST(activity_day AS VARCHAR) || ‘/’ || CAST(activity_month AS VARCHAR) AS activity_ddmm

Thank you so much Rudy. A lot of new things learned only in this post