title: Fetching parents efficiently in rails
body: <p>I'm writing an image gallery.<br>
  An image in the gallery belongs to a project. Each project can have a parent project.<br>
  An example would be <code>project1 &gt; project2 &gt; project3 &gt; image1</code></p>
  
  <p>To allow the admins to use FTP i wanted to have 'pretty' paths to the files.<br>
  Thumbnails and previews are stored in <code>ROOT/previews/&lt;image-id&gt;</code> and <code>ROOT/thumbnails/&lt;image-id&gt;</code> while images are stored in <code>ROOT/images/&lt;project-name&gt;/&lt;project-name&gt;/.../&lt;image-name&gt;</code><br>
  The admin can then simple upload image to the correct folder and the application would discover them and handle everything.</p>
  
  <p>This of course causes the problem of having to fetch all parents when getting the path to an image.  Currently this means first fetching the image, then it's project, then that projects parent and so on.<br>
  If it was always one ancestor (nesting level) the problem would be easy, it's the 0 or more that's the problem.</p>
  
  <p>I don't expect the projects to be very deeply nested but I'm still concerned as having many queries per request is bad.</p>
  
  <p>Is there any way I could avoid this? Maybe fetch all in one?<br>
  Maybe I should skip having FTP uploads all together and only allow drag and drop from the UI?</p>
  
  <p>Maybe I'm worrying too much, I'm only constraining the queries by an indexed field</p>
  
  <hr>
  
  <p>My <code>schema.rb</code> currently looks like this:</p>
  
  <pre><code>ActiveRecord::Schema.define(:version =&gt; 20110827230616) do
  
    create_table "images", :force =&gt; true do |t|
      t.string   "name"
      t.string   "filename"
      t.text     "info"
      t.integer  "project_id"
      t.datetime "created_at"
      t.datetime "updated_at"
    end
  
    add_index "images", ["project_id", "filename"], :name =&gt; "index_images_on_project_id_and_filename", :unique =&gt; true
  
    create_table "projects", :force =&gt; true do |t|
      t.string   "name"
      t.text     "info"
      t.string   "dates"
      t.integer  "parent_id"
      t.datetime "created_at"
      t.datetime "updated_at"
    end
  
    add_index "projects", ["parent_id", "name"], :name =&gt; "index_projects_on_parent_id_and_name", :unique =&gt; true
  
  end
  </code></pre>
  
  <p><code>image.rb</code></p>
  
  <pre><code>class Image &lt; ActiveRecord::Base
    belongs_to :project
  
    # Validation
    validates_presence_of :project_id, :filename
  
    attr_readonly :filename
  
    def path
      if not self.project_id or not self.project
        raise "Called path on an image without a project"
      end
      return File.join(self.project.path, self.filename)
    end
  
    def thumbnail_path
      raise "Called thumbnail_path on an image without an id" unless self.id
      return File.join(Rails.application.config.thumbnail_directory, self.id.to_s)
    end
  
    def preview_path
      raise "Called preview_path on an image without an id" unless self.id
      return File.join(Rails.application.config.preview_directory, self.id.to_s)
    end
  
    def exists?
      if not File.file? self.path
        return false
      end
  
      return true
    end
  end
  </code></pre>
  
  <p><code>project.rb</code></p>
  
  <pre><code>class Project &lt; ActiveRecord::Base
    belongs_to :parent, :class_name =&gt; 'Project'
    has_many   :children, :class_name =&gt; 'Project', 
               :dependent =&gt; :destroy, :foreign_key =&gt; 'parent_id'
    has_many   :images, :dependent =&gt; :destroy
  
    def safe_name
      return self.name.downcase.gsub(/\s/, '-').gsub(/[^a-z0-9-]/, '_')
    end
  
    def path
      tmp = self
      parents = []
      while tmp
        parents.insert(0, tmp.safe_name)
        tmp = tmp.parent
      end
  
      return File.join(Rails.application.config.image_directory, parents)
    end
  end
  </code></pre>
  
  <p>Example request can look like this:</p>
  
  <pre><code>Started GET "/images/1" for 127.0.0.1 at Sat Sep 03 10:23:30 -0700 2011
    Processing by ImagesController#show as HTML
    Parameters: {"id"=&gt;"1"}
    Image Load (0.2ms)  SELECT "images".* FROM "images" WHERE "images"."id" = ? LIMIT 1  [["id", "1"]]
    Project Load (0.2ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 2 LIMIT 1
    Project Load (0.2ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 1 LIMIT 1
    Project Load (0.1ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" IS NULL LIMIT 1
  </code></pre>