search the records using two keys in two tables in rails

Question

I have models named User and Service

User model has the following columns: user_name and location_name

Service model has the following columns: user_location and service_name

Now i want to search for the Users who are residing in a particular location and providing a particular service.

The main problem is a user can provide more than one service.

I have achieved the functionality in the following way:

serv1 = Services.find_by_service_name(service_name)

res = []
serv1.each do |s|
   res.push s if s.user_location == location_name
end

Can anybody suggest a better way?

Kirti Thorat · Accepted Answer · 2014-04-07T16:16:32.663

user can provide more than one service

That means User is associated to Service model with has_many relationship, i.e., your models should look like:

class User < ActiveRecord::Base
  has_many :services
  ## ... 
end

class Service < ActiveRecord::Base
  belongs_to :user
  ## ...
end

Next, you must have a foreign_key user_id in services table.

Now i want to search for the Users who are residing in a particular location and providing a particular service.

You can achieve this result with the below mentioned query. Make sure to set the local variables location_name and service_name before executing this query:

User.joins(:services).where('users.location_name = ? and services.service_name = ?', location_name ,service_name)

score 2 · Answer 2 · answered Apr 06 '14 at 16:59

It seems that you're storing a field from the user model in the service model. I'm not sure this is advisable.

The user has a location_name. The service references an entire row in the user table, not a column, which is what location_name is. You can then interact with all aspects of a specific service's user. Additionally, since you stated that a user can provide more than one service, we can assume that more than one user can provide same service. The user must also reference it's services.

With the above structure, your solution can then be:

matched_users = User
  .find_by_location_name(location_name)
  .select { |user| user.service_names.include?(desired_service) }

This will perform one query on your database, User.find_by_location(location_name), which will return users from your desired location. We can then call the ruby enum method select, from which we select the users that provide our desired service.

search the records using two keys in two tables in rails

2 Answers2