(our.umbraco.org and its forums are down at the moment, so posting here)

Needed a way to get the last N blog posts and xslt wasn't cutting it due to the large number of posts it had to wade through. It was tanking the CPU on my dual proc server for 20+ seconds. So, I added this function to my copy of BlogLibrary.cs. Now it's instantaneous.If you can verify the SQL is cool, I'd appreciate it.

        public static XPathNodeIterator GetLatestPosts(int count)
        {
            // get the blog doctype. Hard coding this is probably not a good idea, but will work for my blog
            umbraco.cms.businesslogic.web.DocumentType docType = umbraco.cms.businesslogic.web.DocumentType.GetByAlias("BlogPost");

            if (docType == null) throw new Exception("Unable to find document type for BlogPost alias");

            ISqlHelper SqlHelper = DataLayerHelper.CreateSqlHelper(umbraco.GlobalSettings.DbDSN);

            string sql = "SELECT TOP " + count + "x.[xml] xml " +
                        "FROM cmsContentXml x JOIN cmsDocument d ON x.nodeId = d.nodeId " +
                            "JOIN cmsContent c ON x.nodeId = c.nodeId " +
                            "JOIN umbracoNode n ON x.nodeId = n.id " +
                            "WHERE d.published = 1 AND " +
                                "c.contentType = @contentTypeId " +
                                "ORDER BY n.createDate desc";

            IRecordsReader rr = SqlHelper.ExecuteReader(
                sql,
                 SqlHelper.CreateParameter("@contentTypeId", docType.Id));

            XmlDocument xd = new XmlDocument();

            XmlNode x = umbraco.xmlHelper.addTextNode(xd, "nodes", "");

            while (rr.Read())
            {
                x.AppendChild(umbraco.xmlHelper.ImportXmlNodeFromText(rr.GetString("xml"), ref xd));
            }

            xd.AppendChild(x);

            return xd.CreateNavigator().Select(".");
        }

Hey Psychlist1972,

Great code - thanks for that. However, this isn't hugely type-safe and the fastest way to get the information you want, using XSLT, is:

<xsl:for-each select="$currentPage/node [@nodeTypeAlias='BlogPost' and position() &lt;= $myCount]">
...
</xsl:for-each>

XSLT is faster than raw DB access - because it's performed directly against the file-based XML cache on disk. File-based access < DB-based access, always.

HTH,

Benjamin