List of most recently modified or created pages
Last Post 11/10/2014 11:12 AM by Joseph Craig. 5 Replies.
Author Messages
Amanda Thomas
Nuke Pro
Nuke Pro
Posts:73


--
10/31/2014 3:56 PM  
I would love a module that shows a list of 5 or so of the most recently modified or created pages on my site.

I am trying to write a SQL query that will do this to get started building such a module, but I'm not a developer so it's a very slow learning process.

select top 5 
TabModules.TabID, TabModules.LastModifiedOnDate, 
TabModules.ModuleTitle, Tabs.TabPath, Tabs.TabName
from TabModules
inner join Tabs
on TabModules.TabID=Tabs.TabID
order by LastModifiedOnDate DESC


This works, except if one page (or one module on that page) was modified more than once in the same time period as the last 5 LastModifiedOnDates, then that page and/or module is returned more than once. I only want to return the 5 most recently updated INDIVIDUAL pages.

There will be other limits to apply to filter out administrative pages. And possibly other things I can't see yet. This may be over my head.
Joseph Craig
DNN MVP
Posts:11667


--
10/31/2014 6:00 PM  
Yes, you can clean that up and eliminate duplicates. I posted an off the cuff modification, and have now deleted it. I may or may not have time to try to fix it over the weekend.

And ... don't try creating a module. Just display the result of the query using the Reports module or (better) SGSV from Tressleworks.ca. SGSV costs a pittance and really rocks!

Joe Craig, Patapsco Research Group
Complete DNN Support
Amanda Thomas
Nuke Pro
Nuke Pro
Posts:73


--
11/03/2014 11:08 AM  
Reading some more, and it seems like I need to partition by the TabID, but I'm having trouble combining that with the Inner Join to get the TabName and TabPath. This is as close as I can get but the syntax still isn't right. I just don't know how to combine it right, and all the SQL tutorials online are for much simpler queries than this. :/

 
select * from
(
select top 5 
TabModules.TabID, TabModules.LastModifiedOnDate, 
TabModules.ModuleTitle, Tabs.TabPath, Tabs.TabName
from TabModules
inner join Tabs
on TabModules.TabID=Tabs.TabID
Rank() over (Partition BY TabID order by LastModifiedOnDate DESC) as Rank
from 
TabModules
) tmp
where Rank = 1
Joseph Craig
DNN MVP
Posts:11667


--
11/05/2014 8:32 AM  
Amanda,

Look into creating stored procedures. You may find it easier that way.

Joe Craig, Patapsco Research Group
Complete DNN Support
Amanda Thomas
Nuke Pro
Nuke Pro
Posts:73


--
11/07/2014 3:02 PM  
I think I may have fixed it by accident. I noticed some broken links coming through where the page URLs had been changed, so I dug around the TabURLs table, and noticed it has a SeqNum fields which seems to number the currently used URL with 0.

select top 5 
TabModules.LastModifiedOnDate, 
TabUrls.Url, 
Tabs.TabName
from TabModules
inner join Tabs
on TabModules.TabID=Tabs.TabID
inner join TabUrls
on TabModules.TabID=TabUrls.TabID
where TabModules.IsDeleted <> 1
and Tabs.ParentId NOT IN (270,117,115)
and TabUrls.SeqNum = 0
order by LastModifiedOnDate DESC


Since I made this change, I haven't noticed any repeating pages, even when expanding to 10 results instead of 5. I will have to test it further, but still pretty exciting.
Joseph Craig
DNN MVP
Posts:11667


--
11/10/2014 11:12 AM  
Great!

Joe Craig, Patapsco Research Group
Complete DNN Support


---