Solved

Ordering Query of Query by Date

Posted on 2013-11-15
8
239 Views
Last Modified: 2013-11-18
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!
0
Comment
Question by:SiobhanElara
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39651734
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
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39651757
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
 
LVL 52

Expert Comment

by:_agx_
ID: 39651772
@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
Webinar: Security & Encryption in the MySQL world

Join Percona’s Solutions Engineer, Dimitri Vanoverbeke as he presents “Security and Encryption in the MySQL world” on Thursday, July 6, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 39651781
Ahhh- Didn't realise that :(
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39651802
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
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 39651851
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
 

Author Closing Comment

by:SiobhanElara
ID: 39656279
Perfect, that was exactly the problem! I didn't realize an alias was required there. (For the record, it works in CF8 too.)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39657952
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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question