CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3performance
iwasrobbed
iwasrobbed

Reputation: 46703

Rails SQL efficiency for where statement

Is there a more efficient method for doing a Rails SQL statement of the following code?

It will be called across the site to hide certain content or users based on if a user is blocked or not so it needs to be fairly efficient or it will slow everything else down as well.

users.rb file:

  def is_blocked_by_or_has_blocked?(user)
    status = relationships.where('followed_id = ? AND relationship_status = ?', 
          user.id, relationship_blocked).first ||
        user.relationships.where('followed_id = ? AND relationship_status = ?', 
          self.id, relationship_blocked).first
    return status
  end

In that code, relationship_blocked is just an abstraction of an integer to make it easier to read later.

In a view, I am calling this method like this:

- unless current_user.is_blocked_by_or_has_blocked?(user)
  - # show the content for unblocked users here

Edit

This is a sample query.. it stops after it finds the first instance (no need to check for a reverse relationship)

Relationship Load (0.2ms)  SELECT "relationships".* FROM "relationships" WHERE ("relationships".follower_id = 101) AND (followed_id = 1 AND relationship_status = 2) LIMIT 1

Upvotes: 1

Views: 308

Answers (1)

idlefingers
idlefingers

Reputation: 32037

You can change it to only run one query by making it use an IN (x,y,z) statement in the query (this is done by passing an array of ids to :followed_id). Also, by using .count, you bypass Rails instantiating an instance of the model for the resulting relationships, which will keep things faster (less data to pass around in memory):

def is_blocked_by_or_has_blocked?(user)
  relationships.where(:followed_id => [user.id, self.id], :relationship_status => relationship_blocked).count > 0
end

Edit - To get it to look both ways;

Relationship.where(:user_id => [user.id, self.id], :followed_id => [user.id, self.id], :relationship_status => relationship_blocked).count > 0

Upvotes: 1

Related Questions