mengu on web programming.

Counting Many To Many Relationships With Grails

I have two domain classes in my Grails app, Post and Tag. I have created an action in my TagController called tagIndex which displays all the tags. So far so good. However I need to count how many posts a tag has. Surely I can implement this with the following: def tagList = Tag.list() tagList.each { println "${it.tagName() (${it.posts.size()})" } The code above will select all the tags from the database and will do a count query on all tags you will display. Absolutely a blood thirst. How can we overcome this? We will do a `Tag.list()` call either way but instead of counting the posts of each tag, we can write a query that gives us all the post counts of all tags. How do we write our query? Writing the count query with Criteria Builder ---------- def c = Tag.createCriteria() def result = c.list{ createAlias('posts', 'posts') projections { rowCount() } groupProperty("id") } The createAlias method is important here. It is what makes the join an inner join instead of a left join. Writing the count query with HQL ---------- def result = Tag.executeQuery( """select tag.id, count(post.id) as count from Tag as tag inner join tag.posts as post group by tag.id""") Using the Result ---------- Now that I have the results like this: [[4, 7], [5, 7], [18, 2], [3, 2], [9, 2], [10, 1], [23, 1], [24, 1], [25, 1], [26, 1], [27, 1], [15, 1], [16, 1], [13, 1]] I can map them in a property that will be visible in my views. def postCount = result.inject([:]) { map, it -> map << [(it[0]): it[1]] } And when I use `` in my views like ``, I will be able to do `${postCount[tag.id]}` and will get the post count for that tag. Coming so far, you will ask, why do I have to use `Tag.list()` and `Tag.count()`? Can I just get my tags along with the counts? Of course you can! You can use the following methods in your domain class and controller. // goes in the domain class. (ie: Tag.groovy) static countTagsWithPosts() { def result = Tag.executeQuery( """select count(post.id) from Tag as tag inner join tag.posts as post group by tag.id""") return result.size() } static getTagListWithPostCount(props) { def result = Tag.executeQuery( """select tag.id, tag.tagName, tag.tagSlug, count(post.id) as count from Tag as tag inner join tag.posts as post group by tag.id order by col_3_0_ desc """, props) return result } // goes in the controller. def tagInstanceList = Tag.getTagListWithPostCount([max: params.max, offset:params.offset]) def tagCount = Tag.countTagsWithPosts() Hope you enjoyed!
Did you enjoy this post? You should follow me on twitter here.

Comments

No comments made for this post.

Leave a Response

No HTML allowed. You can use markdown.
Name*:
E-Mail* (not published):
Web site:
Response: