Fixing up the archive counts in Orchard

For reasons best known to itself, sometimes Orchard gets a little count happy with the number of posts in a given archive year/month and over exaggerates the number of posts present. Fortunately thanks to the open-sourced goodness of Orchard, it's a piece of cake to look through the code and work out where the data displayed in the archive list is generated, then fix it up.

The counts are stored in the table Orchard_Blogs_BlogPartArchiveRecord, with one record per blog, per year, per month. You won't find a direct reference to thet table as it's named based on the module that the model is from (in this instance Orchard.Blogs). The best way to refresh the counts would be to use the Orchard command line to run the command blog build archive but as running this gives me weird errors at the moment, it's to the database!

So, to get the correct counts of posts:

SELECT	Container_id,
	DATEPART([Year], CreatedUtc) [Year],
	DATEPART([Month], CreatedUtc) [Month],
	COUNT(1) [PostCount]
FROM	Common_CommonPartRecord
WHERE	Container_id IN (12, 13)
GROUP
BY	Container_id,
	DATEPART([Year], CreatedUtc) ,
	DATEPART([Month], CreatedUtc)
ORDER
BY	Container_id,
	DATEPART([Year], CreatedUtc) ,
	DATEPART([Month], CreatedUtc)

That can be mixed together with an UPDATE query to fix up the data:

UPDATE	BPAR
SET	BPAR.PostCount = SQ.PostCount
FROM	Orchard_Blogs_BlogPartArchiveRecord BPAR
LEFT
JOIN
	(
		SELECT	Container_id,
			DATEPART([Year], CreatedUtc) [Year],
			DATEPART([Month], CreatedUtc) [Month],
			-- We only want to count published posts, anything that's in draft form shouldn't count!
			SUM(CASE WHEN PublishedUtc IS NULL THEN 0 ELSE 1 END) [PostCountPublished] [PostCount]
		FROM	Common_CommonPartRecord
			WHERE	Container_id IN (12, 13)
		GROUP
		BY	Container_id,
			DATEPART([Year], CreatedUtc) ,
			DATEPART([Month], CreatedUtc)
		) SQ
	ON BPAR.BlogPart_id = SQ.Container_id
	AND BPAR.Year = SQ.Year
	AND BPAR.Month = SQ.Month
WHERE	BPAR.BlogPart_id IN (12, 13)
AND	BPAR.PostCount <> SQ.PostCount

Of course, if you need to use this code then you'll need to substitute the appropriate Blog IDs for where I've used 12 and 13 (my blog and recipe blog respectively!)