SiobhanElara
asked on
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:
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:
Which results in a very unspecific error:
How can I get my feed ordered correctly? Thanks!
<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>
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>
Which results in a very unspecific error:
How can I get my feed ordered correctly? Thanks!
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
@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?
@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?
Ahhh- Didn't realise that :(
The tags (ie MySQL) threw me off at first too :) I only noticed it was a QoQ when I looked closer at the code.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect, that was exactly the problem! I didn't realize an alias was required there. (For the record, it works in CF8 too.)
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 ;-)
Open in new window
You would then need to ORDER BY on newDate