Ordering Query of Query by Date

I'm trying to aggregate 3 RSS feeds and order them by date, like you do. I've collected them with CFFEED and combined them as follows:

<cfquery name="qAllFeeds" dbtype="query">
	SELECT DC_DATE, TITLE, CONTENT, RSSLINK FROM feedquery1
	UNION
	SELECT DC_DATE, TITLE, CONTENT, RSSLINK FROM feedquery2
	UNION
	SELECT DC_DATE, TITLE, CONTENT, RSSLINK FROM feedquery3
	ORDER BY DC_DATE DESC
</cfquery>

Open in new window


The DC_DATE column is formatted like "Wed, 13 Nov 2013 19:55:00 GMT" which, I didn't realize, is treated like a string in QoQ so everything ends up ordered by day name. I did some Googling and found the suggestion to use CAST, so I modified the query to:

<cfquery name="qAllFeeds" dbtype="query">
	SELECT CAST(DC_DATE AS DATE), TITLE, CONTENT, RSSLINK FROM feedquery1
	UNION
	SELECT CAST(DC_DATE AS DATE), TITLE, CONTENT, RSSLINK FROM feedquery2
	UNION
	SELECT CAST(DC_DATE AS DATE), TITLE, CONTENT, RSSLINK FROM feedquery3
	ORDER BY DC_DATE DESC
</cfquery>

Open in new window


Which results in a very unspecific error:

screenshot of error
How can I get my feed ordered correctly? Thanks!
SiobhanElaraAsked:
Who is Participating?
 
_agx_Commented:
Hm... I just noticed you're missing an alias when you CAST the columns.  Adding one seemed to work for me w/CF10:

<cfset theURL = "http://feeds.adobe.com/xml/rss.cfm?query=byCategory&languages=1&categoryId=1">
<cffeed action="read" source="#theURL#" query="feedquery1">

<cfset theURL = "http://feeds.adobe.com/xml/rss.cfm?query=byCategory&languages=1&categoryId=6">
<cffeed action="read" source="#theURL#" query="feedquery2">

<cfquery name="qAllFeeds" dbtype="query">
	SELECT CAST(DC_DATE AS DATE) AS DC_DATE, TITLE, CONTENT, RSSLINK FROM feedquery1
	UNION
	SELECT CAST(DC_DATE AS DATE), TITLE, CONTENT, RSSLINK FROM feedquery2
	ORDER BY DC_DATE DESC
</cfquery>

Open in new window

0
 
Chris StanyonCommented:
If your date is always in that format, then you can use the STR_TO_DATE function

SELECT
   DATE(STR_TO_DATE(DC_DATE, '%a, %d %b %Y %T')) as newDate,
   TITLE,
   CONTENT,
   RSSLINK
FROM feedquery1

Open in new window

You would then need to ORDER BY on newDate
0
 
Chris StanyonCommented:
Just as an addition, the %d in the STR_TO_TIME function assumes your Day number will be 0 padded (01, 02, 03 etc). If it's not (1, 2, 3 etc) then change it to %e
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
_agx_Commented:
@ChrisStanyon - Yes that would probably work w/MySQL but they're using a special in-memory query specific to CF, which doesn't support those functions.

@SiobhanElara - What's the stack trace of the error? If you don't see one, wrap the query in a cftry/cfcatch and dump the error <cfdump var="#cfcatch#">

Also, what are the URL's of one the feeds so we can test them?
0
 
Chris StanyonCommented:
Ahhh- Didn't realise that :(
0
 
_agx_Commented:
The tags (ie MySQL) threw me off at first too :) I only noticed it was a QoQ when I looked closer at the code.
0
 
SiobhanElaraAuthor Commented:
Perfect, that was exactly the problem! I didn't realize an alias was required there. (For the record, it works in CF8 too.)
0
 
_agx_Commented:
Yeah, probably a quirk of QoQ's. I think it'd work w/o an alias in db query. Though ... you'd still need to add an alias anyway. Otherwise you wouldn't be able to access the "DC_DATE" column in CF ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.