Including an association if it exists in a rails query

Update: This may be something that just isn't doable. See this

TLDR: How do you conditionally load an association (say, only load the association for the current user) while also including records that don't have that association at all?

Rails 3.1, here's roughly the model I'm working with.

开发者_如何学C

class User
  has_many :subscriptions
  has_many :collections, :through => :subscriptions
end

class Collection
  has_many :things
end

class Thing
  has_many :user_thing_states, :dependent => :destroy
  belongs_to :collection
end

class Subscription
  belongs_to :user
  belongs_to :collection
end

class UserThingState
  belongs_to :user
  belongs_to :thing
end

There exist many collections which have many things. Users subscribe to many collections and thereby they subscribe to many things. Users have a state with respect to things, but not necessarily, and are still subscribed to things even if they don't happen to have a state for them. When a user subscribes to a collection and its associated things, a state is not generated for every single thing (which could be in the hundreds). Instead, states are generated when a user first interacts with a given thing. Now, the problem: I want to select all of the user's subscribed things while loading the user's state for each thing where the state exists.

Conceptually this isn't that hard. For reference, the SQL that would get me the data needed for this is:

SELECT things.*, user_thing_states.* FROM things
# Next line gets me all things subscribed to
INNER JOIN subscriptions as subs ON things.collection_id = subs.collection_id AND subs.user_id = :user_id
# Next line pulls in the state data for the user
LEFT JOIN user_thing_states as uts ON things.id = uts.thing_id AND uqs.user_id = :user_id

I just don't know how to piece it together in rails. What happens in the Thing class? Thing.includes(:user_thing_states) would load all states for all users and that looks like the only tool. I need something like this but am not sure how (or if it's possible):

  class Thing
    has_many :user_thing_states
    delegates :some_state_property, :to => :state, :allow_nil => true

    def state
      # There should be only one user_thing_state if the include is correct, state method to access it.
      self.user_thing_states.first
    end
  end

I need something like:

Thing.includes(:user_question_states, **where 'user_question_state.user_id => :user_id**).by_collections(user.collections)

Then I can do

things = User.things_subscribed_to 
things.first.some_state_property # the property of the state loaded for the current user.

---

You don't need to do anything.

class User
  has_many :user_thing_states
  has_many :things, :through => :user_thing_states
end

# All Users w/ Things eager loaded through States association
User.all.includes(:things)

# Lookup specific user, Load all States w/ Things (if they exist for that user)
user = User.find_by_login 'bob'
user.user_thing_states.all(:include => :things)

Using includes() for this already loads up the associated object if they exist.

There's no need to do any filtering or add extra behavior for the Users who don't have an associated object.

---

Just ran into this issue ourselves, and my coworker pointed out that Rails 6 seems to include support for this now: https://github.com/rails/rails/pull/32655

---

*Nope, didn't solve it :( Here's a treatment of the specific issue I seem to have hit.

Think I've got it, easier than expected:

class Thing
  has_many :user_thing_states
  delegates :some_state_property, :to => :state, :allow_nil => true

  scope :with_user_state, lambda { |user| 
     includes(:user_thing_states).where('user_thing_states.user_id = :user_id 
                                         OR user_thing_states.user_id IS NULL', 
                                        {:user_id => user.id}) }

  def state
    self.user_thing_states.first
  end
end

So:

Thing.with_user_state(current_user).all

Will load all Things and each thing will have only one user_question_state accessible via state, and won't exclude Things with no state.

---

Answering my own question twice... bit awkward but anyway.

Rails doesn't seem to let you specify additional conditions for an includes() statement. If it did, my previous answer would work - you could put an additional condition on the includes() statement that would let the where conditions work correctly. To solve this we'd need to get includes() to use something like the following SQL (Getting the 'AND' condition is the problem):

LEFT JOIN user_thing_states as uts ON things.id = uts.thing_id AND uqs.user_id = :user_id

I'm resorting to this for now which is a bit awful.

class User
  ...

  def subscribed_things
    self.subscribed_things_with_state + self.subscribed_things_with_no_state
  end

  def subscribed_things_with_state
    self.things.includes(:user_thing_states).by_subscribed_collections(self).all
  end

  def subscribed_things_with_no_state
    Thing.with_no_state().by_subscribed_collections(self).all
  end

end