Query a User by name and associated location but return user with only the matching location not all locations

Question

Is it possible to use pg_search to query a User that has many locations but only return the User and the matching associated object? So if a User has two locations from NY and another User has locations in NY and FL. Can we do User.search_name_and_location('F NY') and get back everyone with F in their name, with associated objects that match NY?

Answer 1

First query for the user with the required matching pattern.

user_pattern = "N"
users = User.where("name like ?", "%#{user_pattern}%")

Next use a similar query for getting the locations with required Pattern

location_pattern = "NY"
locaations_array = users.includes(:locations).collect(&:locations).flatten // eager loading locations and converting them to 1D array.
locations = locations_array.select{ |location| location.name.casecmp("#{location_pattern}") == 0 } // this does a case ignore comparison

Since both are separate models, It's difficult to get both using a single query. So, I'm using a hash to frame the results

result = Hash.new(users: users, locations: locations)
result[:users] = users
result[:locations] = locations

Or a simple method to do all of the above

def users_with_locations(user_pattern, location_pattern)
  result = Hash.new
  users = User.where("name like ?", "%#{user_pattern}%")

  locations_array = users.includes(:locations).collect(&:locations).flatten // I'm eager loading all the locations and converting them to a 1D array
  locations = locations_array.select{ |location| location.name.casecmp(location_pattern) == 0 } // this does a case ignore comparison 

  result[:users] = users
  result[:locations] = locations
  result
end