Sort Tags by popularity, or: how to sort a DataObject by count of many_many relation

Today I did some minor updates on a site with a blog and wanted to show the most popular tags. But how can I sort a DataObject by the number of it's $many_many or $belongs_many_many relations?

Stuck on this issue I asked on IRC and Barry told pointed me to the right query, how it would work with good ol' plain SQL:

SELECT bt.ID, bt.Title, COUNT( * )
FROM BlogTag bt
LEFT JOIN BlogPost_Tags bpt ON bpt.BlogTagID = bt.ID
GROUP BY bt.ID
ORDER BY COUNT( * ) DESC

OK, there we go. Creating a DataList and a left join is pretty straigtforward (once you know your table names), so i got this pretty quickly:

$tags = BlogTag::get()
	->leftJoin('BlogPost_Tags','bpt.BlogTagID = BlogTag.ID','bpt')
	->sort('Count(*) DESC')
	->limit($limit);

which is quite useless now, as we have to GROUP BY ID. But unfortunaltey DataList doesn't have a simple group by method.

Am I stuck? Can't I do this (maybe not so) simple query with my beloved SilverStripe ORM?

Well, each DataList produces a DataQuery, which in turn has a groupBy() method. And DataList has a method called "alterDataQuery" which wants a callback and returs a fresh, new and modified DataQuery (hidden pun for my German speaking readers). So i came up with this solution in my Page_Controller:

public function getPopularBlogTags($limit = 5) {
	$tags = BlogTag::get()
		->setQueriedColumns(['ID', 'Title', 'Count(*)'])
		->leftJoin('BlogPost_Tags','bpt.BlogTagID = BlogTag.ID','bpt')
		->sort('Count(*) DESC')
		->alterDataQuery(function($query){
			$query->groupBy('BlogTag.ID');
		})
		->limit($limit);

	return $tags;
}

and in my template i can now loop over the most popular tags like:

<% cached 'tags-widget', $List('BlogPost').max('LastEdited'), $List('BlogTag').count() %>
    <% loop $PopularBlogTags %>
        <a href="$Link" title="$Title">$Title ($BlogPosts.Count())</a>
        <% if not $Last %> | <% end_if %>
    <% end_loop %>
<% end_cached %>

Thanks to Barry and Mark on IRC that helped me solving this issue.

Rate this post

Post your comment

Comments

No one has commented on this page yet.

RSS feed for comments on this page | RSS feed for all comments