Closure Tree hierarchy counting

We use a nifty gem called Closure Tree in our Rails app to manage our application’s file manager hierarchy. It stores the entire hierarchy in a separate table so you don’t have to traverse the parent IDs recursively every time you want to find the ancestors or descendants.

But what if you want the counts of all the child objects that meet a certain criteria? You could sum all of them up from the bottom of the tree using Ruby, but much better to let your database do this for you using count(*).

For instance – our files and folders are stored in the same table, and we have an identifier. Furthermore, not every user has permission to see every file.

We came up with a simple enough class method to return a hash of each folder ID you pass in and the number of files in each:


base_scope = FileBase.not_folders.
joins("INNER JOIN file_basis_hierarchies ON file_basis_hierarchies.descendant_id = file_bases.id").
where("file_basis_hierarchies.ancestor_id IN (?)", folder_ids)

base_scope = base_scope.allowed_to_see(user) if user

Hash[
base_scope.allowed_to_see(user).
group('file_basis_hierarchies.ancestor_id').
select("file_basis_hierarchies.ancestor_id as id, count (*) as file_count").map { |dc| [dc.id, dc.file_count] }
]

Posted in Uncategorized

Leave a Reply

Your email address will not be published. Required fields are marked *

*