List tree items only with associations (+counts)

[dmitry]

Is there are some easy approach to get all the items of a tree as a nested hash (hash_tree) but only these that have any associations and return the number associated record to the item (with descendants)?

In my opinion it's a common task. How I'm doing it right now:

class Location < ActiveRecord::Base
  acts_as_tree

  has_many :places

  class << self
    def hash_tree_with_places(options = {})
      build_hash_tree(hash_tree_scope_with_places(options[:limit_depth]))
    end

    def hash_tree_scope_with_places(limit_depth = nil)
      select('COUNT(properties.id) AS properties_count, locations.*').
        joins(:descendant_hierarchies).
        joins('JOIN properties ON properties.location_id = location_hierarchies.descendant_id').
        from("(#{hash_tree_scope.to_sql}) locations").
        group('locations.id')
    end
  end
end

But I don't like it because:

• Cannot add additional scope to the table scope (at least easily; current implementation, a little bit extended, uses option for this kind of problem).
• This functionality should be embedded in the library.

Lets find the better solution and include it in the library.

[seuros]

This is a specific case, i don't think it good to add all the edge cases to the library.

[dmitry]

Isn't it a common case to build the select (options) or navigation trees?

[seuros]

You can extend these methods : https://github.com/mceachen/closure_tree/blob/de3c605da9f24430c365b82a9c94bb81cd4e1c1e/lib/closure_tree/hash_tree.rb

[dmitry]

That's what I made actually. You can see it in the provided code above. But there are few drawbacks, that I've listed in the topic message.

[seuros]

And i said that the extra customization is application specific and not general case.
Anyway, you can write a PR with correct specs and send it. @mceachen will decide if that mergable or no.

[dmitry]

I would like to discuss with someone who also would like to see the same functionality in the closure_tree core... because I don't know the best way how to implement it. I think to find out the best approach, we should discuss first, and then implement. So I would like to wait till someone else will respond on this "issue", please don't close it for now.

[mceachen]

Hi, I actually have to provide exactly this sort of metadata in my app as
well.

I found that when I was using mysql, the performance of the count() was
horribly bad with nontrivial numbers of labels (it might be ok with
postgres—I haven't tried it yet). I had to resort to a materialized value
of the count() in the db instead of doing the count(*) for every query.

If you went with this approach, you wouldn't have to pass a custom select
to the hash_tree method—just a custom where clause.

[AlexVPopov]

👍 for this one. I also need this and currently I am using @dmitry's approach.

[AlexVPopov]

Just to add: my idea is that if you have tag foo, which cannot be directly applied to, say photo or a product, but contains child tags bar and baz and both of these are applied to one product, than the products count of foo must be 2.

[mceachen]

Rather than have this functionality in the library, would a plugin make sense here?

In retrospect, all the deterministic ordering functionality, for example, was very invasive to this gem, and by having documented callback hooks, I may have been less complicated to have pulled it into a new sub-gem.

What RDBMS are you guys using?

[dmitry]

MySQL here.

[dmitry]

@mceachen why not to make this code https://github.com/mceachen/closure_tree/blob/de3c605da9f24430c365b82a9c94bb81cd4e1c1e/lib/closure_tree/hash_tree.rb#L26 more flexible?

[seuros]

@dmitry Do you want to send a PR with a more flexible approach ?

[dmitry]

@seuros not really, don't have a time right now, and I'm not required in such functionality anymore. Just a suggestion that it may be written in more flexible way (some parts of this big method may be extracted, as an example).