In 30: Yahoo Pipes in ColdFusion
In 30I like to spend some time writing proof of concept (POC) code. If such an experiment works I will often use it in an application and write it properly. Like many I have been impressed with the Yahoo Pipes. At a basic level Yahoo Pipes puts together two or more RSS feeds. Here I'll show how to do the backend in CF.
Ok first I made a pipe for myself of Ben Forta and Ray Camden's blog feeds which you can see here.
So, CF 7 does not handle RSS feeds natively but fortunately Ray Camden (does the man ever sleep? :)) has written a RSS.cfc that will read and write feeds. So with that downloaded I created an array of feeds.
<cfset variables.arFeeds = ArrayNew(1)> <cfset arrayAppend(variables.arFeeds,"http://feeds.feedburner.com/RaymondCamdensColdfusionBlog")> <cfset arrayAppend(variables.arFeeds,"http://www.forta.com/blog/rss.cfm?mode=short")>
Next lets create an object of RSS and then loop over that array and put the results of the feed in an array.
<cfset variables.rss = createObject("component","rss")>
<cfset variables.arEntries = ArrayNew(1)>
<cfloop from="1" to="#arrayLen(variables.arFeeds)#" index="variables.i">
<cfset arrayAppend(variables.arEntries,variables.rss.getEntries(variables.arFeeds[variables.i]))>
</cfloop>
<cfdump var="#variables.arEntries#" expand="false">So now we have an array with a query of feeds but we really want to make one query. Time to step up Query-of-Queries! And the UNION operator. Though one limitation is that Q-o-Q's need to refer to a query that does not have a dot in it. D'oh. However, the code below will get around that by setting a variable to the array with the query. Not fully sustainable but this is a POC.
<cfquery name="pipeSelect" dbtype="query"> <cfloop from="1" to="#arrayLen(variables.arFeeds)#" index="variables.i"> <cfswitch expression="#variables.i#"> <cfcase value="1"> <cfset qry1 = arEntries[variables.i]> SELECT qry1.title, qry1.[date], qry1.description, qry1.link FROM qry1 </cfcase> <cfcase value="2"> <cfset qry2 = arEntries[variables.i]> SELECT qry2.title, qry2.[date], qry2.description, qry2.link FROM qry2 </cfcase> </cfswitch> <cfif variables.i neq arrayLen(variables.arFeeds)> UNION </cfif> </cfloop> ORDER BY [date] desc </cfquery>
Not the qry1.[date] notation to get around reserved words in Query-of-Queries.
Now a little outputting:
<cfoutput query="pipeSelect"> <p style="font-family: Verdana;"><a href="#pipeSelect.link#">#pipeSelect.title#</a> on #dateFormat(pipeSelect.date,"short")# #timeFormat(pipeSelect.date,"short")#<br /> #pipeSelect.description#</p> </cfoutput>
And the concept has been proved!
Full code:
<cfset variables.arFeeds = ArrayNew(1)>
<cfset arrayAppend(variables.arFeeds,"http://feeds.feedburner.com/RaymondCamdensColdfusionBlog")>
<cfset arrayAppend(variables.arFeeds,"http://www.forta.com/blog/rss.cfm?mode=short")>
<cfset variables.rss = createObject("component","rss")>
<cfset variables.arEntries = ArrayNew(1)>
<cfloop from="1" to="#arrayLen(variables.arFeeds)#" index="variables.i">
<cfset arrayAppend(variables.arEntries,variables.rss.getEntries(variables.arFeeds[variables.i]))>
</cfloop>
<cfdump var="#variables.arEntries#" expand="false">
<cfquery name="pipeSelect" dbtype="query">
<cfloop from="1" to="#arrayLen(variables.arFeeds)#" index="variables.i">
<cfswitch expression="#variables.i#">
<cfcase value="1">
<cfset qry1 = arEntries[variables.i]>
SELECT qry1.title,
qry1.[date],
qry1.description,
qry1.link
FROM qry1
</cfcase>
<cfcase value="2">
<cfset qry2 = arEntries[variables.i]>
SELECT qry2.title,
qry2.[date],
qry2.description,
qry2.link
FROM qry2
</cfcase>
</cfswitch>
<cfif variables.i neq arrayLen(variables.arFeeds)>
UNION
</cfif>
</cfloop>
ORDER BY [date] desc
</cfquery>
<cfoutput query="pipeSelect">
<p style="font-family: Verdana;"><a href="#pipeSelect.link#">#pipeSelect.title#</a>
on #dateFormat(pipeSelect.date,"short")# #timeFormat(pipeSelect.date,"short")#<br />
#pipeSelect.description#</p>
</cfoutput>
If this wasn't POC how would you avoid copying to a separate variable?
Just replace your cfquery with this and it will dynamically create the variables, and will also allow the addition of more feeds without adding cases:
SELECT qry#variables.i#.title,
qry#variables.i#.[date],
qry#variables.i#.description,
qry#variables.i#.link
FROM qry#variables.i#
UNION
ORDER BY [date] desc
<cfquery name="pipeSelect" dbtype="query">
<cfloop from="1" to="#arrayLen(variables.arFeeds)#" index="variables.i">
<cfset "qry#variables.i#" = arEntries[variables.i]>
SELECT qry#variables.i#.title,
qry#variables.i#.[date],
qry#variables.i#.description,
qry#variables.i#.link
FROM qry#variables.i#
<cfif variables.i neq arrayLen(variables.arFeeds)>
UNION
</cfif>
</cfloop>
ORDER BY [date] desc
</cfquery>
Loading....