Solved

Ordering Query of Query by Date

Posted on 2013-11-15
8
229 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
  • 4
  • 3
8 Comments
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
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 42

Expert Comment

by:Chris Stanyon
Comment Utility
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_
Comment Utility
@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
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
Ahhh- Didn't realise that :(
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 52

Expert Comment

by:_agx_
Comment Utility
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
Comment Utility
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
Comment Utility
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_
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now