Postgres query on relations

Question

I have a query:

@profiles = Profile.where('whatami LIKE ? AND expectedsalaray <= ?', '%' + @job.title + '%', @job.salary)

Profiles is associated with another model called cities profile.cites How can i check this associated model in the where statement ?

so something like:

Profile.where('profiles.city LIKE ?', @job.city)

Here are my associations:

class City < ActiveRecord::Base
  has_and_belongs_to_many :profiles
end

class Profile < ActiveRecord::Base
  has_and_belongs_to_many :cities
end

Can you update your question and include your models for the two tables? We just need the relation info. has_one, has_many, belongs_to. — Dan
– Dan, Commented Jun 24, 2016 at 18:47
I'm not sure has_and_belongs_to_many is the correct association for what you are trying to accomplish. I also don't know anything except what you've posted so I might be missing something. Do you have a table called cities_profiles that has city_id and profile_id? That's usually required for this type of relationship. guides.rubyonrails.org/… — Dan
– Dan, Commented Jun 27, 2016 at 15:42

Dan · Accepted Answer · 2016-06-27 15:34:10Z

1

I'm going to take a crack at answering this with the assumption your models have declared their associations correctly.

class Profile
  belongs_to :city
end

class City
  has_many :profiles
end

@profiles = Profile.where('whatami ILIKE ? AND expectedsalaray <= ? AND
cities.name ILIKE ?', "%#{@job.title}%", @job.salary, "%#{@job.city}%").joins(:city)

.joins() will add a left join to your SQL/Postgres allowing you to reference columns from the joined table.

I've added interpolation to your like wildcard to make it a little shorter vs. all the plus signs.

Using ILIKE instead of LIKE can help prevent problems related to case sensitivity. Thanks to @Shayna for reminding me of that.

edited Jun 27, 2016 at 15:34

answered Jun 24, 2016 at 18:53

Dan

1,2892 gold badges18 silver badges33 bronze badges

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

2 Comments

Gurmukh Singh Over a year ago

I'm getting an error 'Association named 'city' was not found on Profile; perhaps you misspelled it?' Iv updated my question with the associations

Shayna Over a year ago

Or instead of converting everything to the same case you can use ILIKE for case-insensitive matching.

Collectives™ on Stack Overflow

Postgres query on relations

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