CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlactiverecord
Geo
Geo

Reputation: 96827

How could I query these 3 associated models?

Imagine these models:

class User
  belongs_to :profile
  # has email here
end

class Profile
  has_one :user
  # has first_name,last_name
end

and

class Post
  belongs_to :profile
  # has title,content
end

Now, I would like to query all posts ( do a LIKE "%substring%" ) on the user's email. I would prefer to not have to write it with map/selects as I think it would generate pretty inefficient code. I tried something like that:

class Post
  def self.with_user_email_like(email)
    self.joins(:profile).where("profile.email LIKE ?","%#{email}%")
  end
end

The thing is, I know somehow I should have a profile.user.email in the condition above, but I just can't get it to work. Any suggestions?

Upvotes: 0

Views: 69

Answers (2)

Suborx
Suborx

Reputation: 3669

Try this

class Post
  belongs_to :profile
  scope :with_user_email_like, lambda{|email| joins(:profile => :user).where("users.email LIKE %?%", email)} 
end

Upvotes: 1

Mischa
Mischa

Reputation: 43298

Well you are almost there, but since email is in the users table you have to join that too:

self.joins(:profile => :user).where("users.email LIKE ?","%#{email}%")

Upvotes: 1

Related Questions