Rails Find all nested Albums or Collections in a Collection-CodePudding

Here are my two models:

Collection:

class Collection < ActiveRecord::Base

  has_many :children, class_name: "Collection", foreign_key: "parent_id"
  belongs_to :parent, class_name: "Collection", foreign_key: "parent_id", optional: true
  has_many :albums, dependent: :nullify

end

Album:

class Album < ActiveRecord::Base

  belongs_to :collection, optional: true
  has_many :photos, dependent: :destroy

end

I need a way to find all Albums and/or Collections within a parent Collection.

Any ideas as to how I can accomplish this?

This will allow me to access a list of all the photos within a parent Collection etc.

CodePudding user response：

access a list of all the photos within a parent Collection

First approach, recursive call get_photos from the parent Collection to its children then its grand-children and so on ... until there's no more child Collection.

class Collection < ActiveRecord::Base
 # ...
 def get_photos
   albums.map(&:photos).flatten.tap |photos|
     photos << children.map(&:get_photos).flatten
   end
 end
end

This might call many queries.

Second approach, migrating the Collection table to add the collection path column, so if the parent Collection c1 id is 1, its path is /1, its children, for instances, c2 (id: 2) and c3(id: 3) then c2 path is /1/2 and c3 path is /1/3, and so on ... Each time you create a child Collection under a parent Collection, you set its path #{parent_path}/#{id}.

Now you could use the query where('path LIKE ?', "#{parent_path}/%") to get all children collections of the parent Collection.

class Collection < ActiveRecord::Base
 # ...
 def get_photos
   collection_ids = where('path LIKE ?', "#{self.path}/%").pluck(&:id)
   album_ids = Album.where(collection_id: collection_ids).pluck(&:id)
   Photo.where(album_id: album_ids).all
 end
end

you could add directly the collection-path to the Photo table if you want to call only one query.

According to the book SQL Anti-pattern, your problem is Naive Trees (Always Depend on One’s Parent). The second approach is one solution for it - Path Enumeration, others are Nested Sets and Closure Table.