1-888-365-2779
+1-888-365-2779
Try Now
More in this section

Forums / Developing with Sitefinity / quick sql select for published pages

quick sql select for published pages

3 posts, 0 answered
  1. Robert
    Robert avatar
    17 posts
    Registered:
    30 Aug 2011
    15 Oct 2012
    Link to this post
    Hi all
    I have done some quick checks on the SQL backend for Sitefinity 5.1.3450 (and this may apply to earlier versions too but I haven't tested those) to write a select statement that fetches all published page urls in a site.   It turns out that the page urls are stored in a table called sf_page_node in a hierarchical structure.  You can fetch your page urls from this table using the Common Table Element (CTE) structure in SQL 2005 onwards, and the following query seems to work nicely:

    WITH Pages as
    (
     SELECT N.content_id, N.id,CONVERT(uniqueidentifier,NULL) as parent_id, CONVERT(nvarchar(max), '/') + convert(nvarchar(max),N.url_name_) as url
    FROM [dbo].sf_page_node N
    WHERE url_name_ = 'Pages'

     UNION ALL
     SELECT N.content_id,N.id, parent.id, CONVERT(nvarchar(max),parent.url) +CONVERT(nvarchar(max), '/') + convert(nvarchar(max),N.url_name_) as url
    FROM [dbo].sf_page_node N
    JOIN Pages parent
    ON N.parent_id = parent.id
    AND N.approval_workflow_state_ = 'Published'
    )
    SELECT SUBSTRING(url,7,LEN(url)-6) from Pages 
    ORDER BY url 

    I hope this helps some of you
  2. Patrick Dunn
    Patrick Dunn avatar
    237 posts
    Registered:
    03 Nov 2014
    16 Oct 2012
    Link to this post
    Hello,

     Thanks for submitting your resolution to our forums. The same functionality can be achieved via our API.

    private NewsItem GetNewsItemNativeAPI(Guid masterNewsId)
    {
        NewsManager newsManager = NewsManager.GetManager();
        NewsItem item = newsManager.GetNewsItems().Where(newsItem => newsItem.Id == masterNewsId).FirstOrDefault();
       
        if (item != null)
        {
            item = newsManager.Lifecycle.GetLive(item) as NewsItem;       
        }
       
        return item;
    }

    You can then reference the variables of NewsItem to get the url, title, etc. This can be modified easily to get ALL news items as well.

    I hope this helps! Regards,
    Patrick Dunn
    the Telerik team
    Do you want to have your say in the Sitefinity development roadmap? Do you want to know when a feature you requested is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  3. Robert
    Robert avatar
    17 posts
    Registered:
    30 Aug 2011
    16 Oct 2012 in reply to Patrick Dunn
    Link to this post
    Thanks Patrick.  I know it is always preferable to use the API if possible, however I required this as a quick solution for an external process which I am using to evaluate the effectiveness of the Sitefinity output cache settings and to monitor uncached page load times.  The only other alternative would have been to write a web service that references the API.  Indeed I am going to look at that solution in any case, because it will help with other areas which involve automated inserts and updates to Sitefinity, something that I would never attempt via direct sql updates.  I only posted this query because it is purely a select statement.  I hope I didn't cause any problems.
3 posts, 0 answered