In the first Tag Cloud posting we saw how to use the free REPORTS module to implement a Tag Cloud in just a few minutes. Here in Part 2 we'll update our original solution to make the tags into clickable links...
Recap
In Part 1, "Quickly Create a Tag Cloud with the REPORTS module", we saw how easy it is to repurpose the REPORTS module to serve as a dynamic tag cloud generator. We created a "helper" database function to add a string split feature accessible by our DB queries. With the helper function added to our DB, we simply dropped the tag cloud query into our REPORTS module settings to create the tag cloud. The solution only required about 5 minutes to implement and it was all done through the DNN web interface.
Part 2
The solution from Part 1 was as basic as basic gets. You ended up with a tag cloud that certainly should help with SEO but was otherwise without much use. The tags were not links so you could not click a tag and be taken to the relevant source webpage. Repeating tags were not visually highlighted (with a biggfer font, different color, etc) to help illustrate popular topics. These are both very common tag cloud features that our simple example did not attempt to incorporate.
Here in Part 2 we'll make the tags into "clickable" text links that route the user to the tag's source webpage. Once again we'll implement it very quickly using the REPORTS module. The exercise will help illustrate the limitations of attempting to use only SQL queries for such tasks. After we review the SQL solution, we'll look into how returning your SQL query data as XML can make for simpler code, better performance and an all-around better design.
Clickable Tag Cloud
A "clickable" tag cloud is a tag cloud with links as tags. Each tag link connects us to the page where the tag originated from. This can be handy for end users plus it gives search engines more tag-related info to work with (which should translate to better SEO).
If you didn't follow the example in Part 1, you'll want to go back and implement that solution now as you'll need to add the provided SQL "split" function we used as part of our implementation.
If you've already added the SPLIT function to your DNN database then add a REPORTS module to a test page, open the module SETTINGS and use the following SQL as your query:
declare @myResult varchar(max)
-- A. Collect keywords for split
SELECT
tabid AS [TabID],
keywords AS [Keyword]
INTO #MyTemp1 FROM tabs
WHERE portalid = @portalID
AND LEN(keywords) > 0
AND IsDeleted = 0
-- B. Split list into keyword links
-- NOTE: Remove the extra space after
-- '<' and before '>' characters
SELECT distinct
'< a title="'+value+
'" href="/tabid/'
+ltrim(str(tabid))
+'.default.aspx" >'
+value+'< /a >' as [Tag],
lower(value) as [Keyword],
tabid as [TabID]
INTO #myTemp
FROM tmf_util_Split2
((select #myTemp1.Keyword+',' from #myTemp1
FOR XML PATH('')),',') as tmfs
JOIN #myTemp1 on
#myTemp1.keyword like '%'+tmfs.value+'%'
WHERE LEN(value)>0
-- C. Aggregate keyword links into single string
SET @myResult =
(SELECT Tag + ' .. ' AS [text()]
FROM #myTemp mt
ORDER BY TabID
FOR XML PATH(''))
DROP TABLE #myTemp
DROP TABLE #myTemp1
-- D. Return with HTML unencoded tag bracket values
SELECT REPLACE
(REPLACE(@myResult,'<','<'),'>','>')
After you've added this SQL as your REPROTS module query, click UPDATE to save it and see the results. You should see your various page keywords rendered together in a "cloud" as individual text links (you DID rememebr to add some keywords to a few DNN pages, didn't you?)
The Query
The first thing to notice here is that our SQL is a bit more complex compared to the Part 1 example. We're now using two temp tables instead of one plus our temp tables consist of additional columns to provide needed data for building tag links and for simple tag sorting. Here's a quick overview of each section as defined by the code comment lines:
A. Collect keywords for split
This section grabs all the DNN page keywords and assembles them into a temp table along with the keywords' source tabid value (which we'll need in order to build the links for the tabs). We ignore any pages with no defined keywords and any pages marked as "deleted". Note that the variable "@portalID" in this case is actually a REPORTS module token rather than a SQL variable.
B. Split list into keyword links
Here's where the heavy lifting happens. Using our temp table from section A as our data source, each set of keywords is split into individual entries and added to a second temp table. The split operation is needed so we can treat each keyword as an individual tag in our cloud. As the keywords are added to the second temp table, the needed HTML is added that will eventually be rendered as text link tags. We employ a simple JOIN operation with a copy of distinct keywords in order to filter out any duplicates. Note that by doing this we will NOT link to all pages for repeated keywords but instead only the page with the lowest tabid value. If you prefer to include repeated keywords then comment out the "JOIN" line and the line immediately after.
C. Aggregate keyword links into single string
Rather than create a tag cloud table, we want a series of inline tags that will wrap within our module's HTML container element. The easy way to do this in SQL is with XML serialization (if you are using MS SQL 2005 or newer). The "FOR XML PATH" clause allows us to combine all entries from a table column into a single string of data. Unfortunately this means that HTML gets encoded. This is quite necessary for data to be returned as XML but in our case we're not interested in XML -- we just want to leverage the serialization feature to concatenate all our table entries into a single string. We can't "turn off" the encoding action but we can compensate for it, which leads us to the final section...
D. Return with HTML unencoded tag bracket values
The XML serialization from section C changes all of our tag brackets from "<" and ">" to "<" and ">". The simple solution to this problem is to simply change them back. We do this with two REPLACE statements for un-encoding HTML -- one to handle "<" and one to handle ">". There are other characters than can get encoded by XML serialization but these are the only two special characters we're using and, thus, are the only ones we need be concerned with un-encoding.
A Better Option
Our query spends a lot of effort parsing and moving keywords and tabid values between tables several times. It would be much more efficient to manipulate the data from program code rather than through SQL tricks and hacks. A "proper" solution would be a custom module, especially if you want to implement additional features (such as increased font sizes based on the number of keyword repetitions). There is, however, another REPORTS module option we can use to bring a little sanity back to our SQL query (as well as lighten the load on our database server).
Tag Cloud data as XML
Recall that our final two sections, C and D, were used to serialize data into a single string and to "un-encode" special characters that were encoded as part of the serialization. We could do away with C and D completely and instead return the results of section B as-is (the data added to temp table "#temp"). On the module side, we would employ an XSLT (Extensible Stylesheet Language Transformations) file to concatenate the results from the client end of things without need for any HTML un-encoding operations. This would save us a SQL serialization operation and get rid of the nested REPLACE operations on each keyword. Nice, huh?
But wait -- we can do better. Instead of returning the actual HTML for tag links, we could do away with adding any HTML on the SQL side. XSLT is generally much more capable of creating the needed HTML than SQL is. Instead of returning a three-column table, we only need to return data from the keyword and the tabid columns. Our SQL query just got a lot simpler plus we will have separated the data layer from the presentation layer (which is usually a very good thing to do).
BUT WAIT...we can still do better. XSLT is also quite capable of parsing strings so we can do away with our SQL-based SPLIT function and let XSLT tackle the split operation instead (the XSLT "tokenize" function would be especially handy for this). Moving the split operation into XSLT means we could do away with section B. Our query would then be about as simple as possible -- just the code from section A!
Thanks to the REPORTS module's support for XSLT it is possible to quickly and easily (with the right XSLT code) create a clickable Tag Cloud with a very basic SQL query. That sounds like a good subject for a "Part 3" posting so perhaps next time we'll dive into applying transformations with the REPORTS module (if I actually get around to writing a "Tag Cloud Part 3" posting).
Comments?