4

I using Rails 3 and SQLite and I am trying to make an active record query and order the results at the same time using information from another model. I have a resources model(which belongs to user) and a shared_items model to handle users sharing resources with each other. I would like to create a feed of all the resources that a user has created and which have been shared with them. The resource model with a working query:

class Resource < ActiveRecord::Base
  has_many :shared_items, :dependent => :destroy

  default_scope :order => 'resources.shared_items.created_at DESC'
  scope :from_users_sharing_with, lambda { |user| shared_with(user) }

  private

  def self.shared_with(user)

  resource_ids = %(SELECT resource_id FROM shared_items WHERE shared_with_id = :user_id)
  where("id IN (#{resource_ids}) OR user_id = :user_id",
        { :user_id => user })
end

This query creates a nice 'feed' array of all the resources that have been shared with or created by the user but they are ordered by the resource create date rather than creation date of the corresponding shared_item which is what I am looking to do. How can I write the ordering at the database level? I thought I could do the ordering in Ruby (like Ian suggests here) but as I am wanting fetch records 25 at a time in pagination, I don't think I can take that option. Any thoughts appreciated.

Community
  • 1
  • 1
technix
  • 409
  • 1
  • 6
  • 12
  • I have not tested it but following should work: `Resource.includes(:shared_item).where("shared_items.shared_with_id = ? OR user_id = ?", user.id, user.id).order("shared_items.created_at DESC")` – rubish Aug 23 '11 at 19:58
  • Thanks for the response. Would this query be placed instead of the private 'shared_with(user)' method? I have placed it there and I get the exception - ambiguous column name: user_id: SELECT DISTINCT "resources".id FROM "resources" LEFT OUTER JOIN "shared_items" ON "shared_items"."resource_id" = "resources"."id" WHERE (shared_items.shared_with_id = 1 OR user_id = 1) ORDER BY shared_items.created_at DESC LIMIT 30 OFFSET 0 – technix Aug 25 '11 at 11:36
  • change `user_id` to `resources.user_id` to avoid the error. – rubish Aug 25 '11 at 12:24

1 Answers1

3
class Resource < ActiveRecord::Base
  has_many :shared_items, :dependent => :destroy

  scope :from_users_sharing_with, lambda { |user| 
    includes(:shared_item).
      where("shared_items.shared_with_id = ? OR resources.user_id = ?", user.id, user.id).
      order("shared_items.created_at DESC")
    # might need to add `select("DISTINCT resources.*")` at the end 
  }
end
rubish
  • 10,887
  • 3
  • 43
  • 57
  • This worked a treat. Just had to change includes(:shared_item) to (:shared_items). Using terms/words from exceptions that have cropped up I found info about 'Eager loading of associations' which I assume is what this query is performing? Thanks for the help. – technix Aug 26 '11 at 13:33
  • @technix Basically, i wrote it this way to reduce the two queries to one, in your scope, so that you can sort on associated model. Eager loading is a side effect, which would only be helpful if you are using shared items for resources returned by this scope. You may take note that if you add `select(DISTINCT resources.*)` at the end, you will lose the eager loading benefits. – rubish Aug 26 '11 at 13:50