Rails SQL efficiency for where statement

Question

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

Is relationship_blocked essentially "true" or "false", or do you have additional states (0, 1, 2, 3...) which it can occupy? — jefflunt
– jefflunt, Commented May 3, 2011 at 1:21
Also, when you display this view, in your log file (e.g. development.log, if you're in the "development" environment) you should see the full SQL statement. How many milliseconds is it currently taking to run, out of curiosity? — jefflunt
– jefflunt, Commented May 3, 2011 at 1:26
@normalocity: It can occupy one of a few integer states. It typically takes 0.1 - 0.2ms to run the statement, but that's on a database without many records. All of the columns it is searching are indexed, so it's more the efficiency of the statement that will be the issue.. — iwasrobbed
– iwasrobbed, Commented May 3, 2011 at 11:20

idlefingers · Accepted Answer · 2011-05-03 12:00:15Z

1

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

edited May 3, 2011 at 12:00

answered May 3, 2011 at 11:36

idlefingers

32.1k5 gold badges85 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

iwasrobbed Over a year ago

How would this be able to check the reverse relationship though? For instance, right now this would only check if current_user (or whatever user you're calling the method on) had a relationship that either included himself or the other user, but you'd need to check the reverse of that as well i.e. user.relationships.where...

idlefingers Over a year ago

ah, ok, I didn't notice that in your example. Still, if the first query returns nil, it will end up running the second query. I guess it depends how important that is to you. You could still get around it and turn it into a single query by running a similar query to my example directly on Relationship rather than relationships or user.relationships and just pass more into it (.where(:user_id => [user.id, self.id])).

Collectives™ on Stack Overflow

Rails SQL efficiency for where statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related