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
.