July 19, 2007

Viewing Most Popular Links - Revisited

A little over a month ago I posted a SQL Query that can be used to find the most popular links on a given portal. In that example if you had links that were being tracked that were files it would only show the fileid for the file and not the file name. Now since there has been a fair amount of reader demand I have created a new version of this query to actually show the URL for URL based links and to show the file name for file based links.

To successfully parse the "fileid=xx" portion of the results when needed I use the substring function to grab just the id so I can join to the files table to grab the file name. Due to the different methods needed for files and links I have done this via two queries and then used the "UNION" statement to combine the results for display. For you to use this on your portal you will need to be sure to modify the portalid value to match that of your portal.

--Get standard links
SELECT m.ModuleTitle,
        t.url,
        t.clicks,
        t.LastClick
FROM urltracking t
    
INNER JOIN Modules m
        
ON (t.ModuleId m.ModuleId)
WHERE t.PortalId 0
    
AND t.trackclicks 1
    
AND t.urltype <> 'F'
--Combine with the others
UNION
--Get File links
SELECT m.ModuleTitle,
        f.[FileName] 
as url,
        t.clicks,
        t.lastclick
FROM urltracking t
    
INNER JOIN Modules m
        
ON (t.moduleid m.moduleid)
    
INNER JOIN Files f
        
--Grab the file id from the url field for the join
        
ON (CAST(SUBSTRING(t.url, 8LEN(t.url)) as intf.fileid)
WHERE t.urltype 'F'
ORDER BY t.clicks DESC, url

Please post any comments/questions below

tags: DNN, SQL
comments powered by Disqus

Content provided in this blog is provided "AS-IS" and the information should be used at your own discretion.  The thoughts and opinions expressed are the personal thoughts of Mitchel Sellers and do not reflect the opinions of his employer.

Content Copyright

Content in this blog is copyright protected.  Re-publishing on other websites is allowed as long as proper credit and backlink to the article is provided.  Any other re-publishing or distribution of this content is prohibited without written permission from Mitchel Sellers.