5

I have two models, Location and Basic, I'm using pg_search and geocoder gem, how do I add scope to my pg_search_scope with geocoder near method or if there's another method to do so?

Location model has the latitude and longitude columns.

In my controller I'm able to do this:

# this allows me to search near the Location model
@business = Location.near(params[:loc], 15)

# this searches through the pg_search_scope
@term = Basic.search(params[:q])

#I'm trying to combine @business and @term

And with Basic model I have this:

pg_search_scope :search, :against => [:first_name, :last_name], 
  :associated_against => {
    :services => [:service, :description]
  },
  :using => {
    :tsearch => {:disctionary => "english" }
  }

I'd like to combine both @business and @term into one (@search) Is this possible?

Thanks

++++++++++++++++

So in my Basic model:

class Basic < ActiveRecord::Base
  belongs_to :location

  has_many :services, as: :servicable

  pg_search_scope :search, :against => [:first_name, :last_name], 
    :associated_against => {
      :services => [:service, :description]
    },
    :using => {
      :tsearch => {:disctionary => "english" }
    }

end

So in my model, I have services that is linked with basic. So when I search a name or a service results pop out. But I'm trying to only display results near a certain location that a user inputs, thats why I have a location_id column in my Basic table.

This is my Location model

class Location < ActiveRecord::Base
  has_many :basics
  geocoded_by :new_address
  after_validation :geocode, :if => :new_address_changed?
  def gmaps4rails_address
    :new_address
  end
  def new_address_changed?
    attrs = %w(street city state)
    attrs.any?{|a| send "#{a}_changed?"}
  end  
end

So I'm trying to chain the methods. Ideally the application would search all nearby results first, and then search the terms that matches within the nearby results, then display only those that are applicable.

hellomello
  • 8,219
  • 39
  • 151
  • 297
  • Could you provide the output of `@business` and `@term` as well as describe what you want `@search` to be? – Dan Grahn Nov 15 '13 at 15:34
  • @andrewliu Could you also specify relations between `Location` and `Basic` models? – skalee Nov 15 '13 at 18:44
  • Your example for fetching those is from two different classes with no relations between. By that I mean setting `@term` doesn't depend on the results from `@business` so combining that into a single scope is probably not what you're looking for - but I could be wrong. Are you trying to create one array of results based on the values in `@term` and `@business`? – Brandon Buck Nov 15 '13 at 18:47
  • @skalee I edited my answer, hopefully it helps? – hellomello Nov 16 '13 at 01:40
  • @izuriel yes, I'm trying to create one array, that applies to both search fields I have. I updated my question if it makes more sense – hellomello Nov 16 '13 at 01:40
  • @screenmutt I updated my question. `@business` and `@term` are completely separate from each other. But because these two models are associated somehow, I'd like to chain the methods `near` and `search` so that when user types in a term and a location, the correct result will poop out – hellomello Nov 16 '13 at 01:44
  • What type of order are you looking for - is it not enough to just join the two result lists? Do you need a specific sort mechanism? If you know you're going to use all the values fetched from both results what's wrong with `@business.load + @term.load`? – Brandon Buck Nov 16 '13 at 20:14
  • @izuriel adding it like that won't work, because it will search all terms and all locations. But I only want to search the terms within the location specified. `pg_search` is good for my application because its an easy way to implement full-text searching. – hellomello Nov 16 '13 at 23:32
  • I think you missed it... `@business` is your near search and `@term` is your `pg_search` - Did I miss something? You're joining multiple searches, or do you mean to search only those `Basic` items that are also near a certain location? – Brandon Buck Nov 17 '13 at 06:50
  • Perhaps try `merge` like `Basic.search(params[:query]).includes(:location).merge(Location.near(params[:loc], 15)` – Brandon Buck Nov 17 '13 at 06:53
  • @izuriel hmm i thought it would work, but I'm getting an error `ActiveRecord::StatementInvalid in Static#search` and then the error is `PG::UndefinedTable: ERROR: missing FROM-clause entry for table "locations"` and a bunch of other line codes. – hellomello Nov 17 '13 at 11:21
  • Change the include to `:locations` – Brandon Buck Nov 17 '13 at 18:30
  • @izuriel dangit, its still giving me the same errors when changing it to plurarl. – hellomello Nov 17 '13 at 20:02
  • 1
    I always user `includes` before any other call, if you can do that try it, otherwise try replacing `includes` with `joins`. – Brandon Buck Nov 17 '13 at 20:24
  • @izuriel yes it works! thank you!!! I'm not sure why the includes wouldn't work? But joins do work. Would you like to answer this question so I can send you some points? Thank you! – hellomello Nov 18 '13 at 05:21
  • @izuriel also, I just realized that my id doesn't show up? when I have joins, I only have location_id (from `Basic`) and id (from `Location`), but no id (from `Basic`). I need the id from Basic so I can create a link to a page. – hellomello Nov 19 '13 at 04:57
  • 1
    @andrewliu I'll draft up an answer when I help you solve this issue, I'll look into finding out about lost IDs. – Brandon Buck Nov 19 '13 at 06:50
  • @andrewliu `includes` and `joins` *can* result in the same thing but don't have to. `includes` forces objects to be created for the included association, but no `JOINS`. `joins` forces an `INNER JOIN` on the association but does not include it in the `SELECT` (and creates no objects). It is legitimate, to combine the two, forcing a `JOIN` with eager loading. But be aware of the difference between `INNER` and `OUTER JOIN`. `includes` does the latter. I suggested something similar [here](http://stackoverflow.com/questions/19322580/optimize-difficult-query-possibly-with-squeel/19527607#19527607) – CMW Nov 19 '13 at 10:05
  • @izuriel thank you so much for your help. Let me know when you find lost id solution, and then I'll put up another bounty for your answer. Right now I had to get the ID from looping `<%= getID = Basic.find_by_first_name_and_last_name(s.first_name, s.lastName) %>` – hellomello Nov 26 '13 at 18:02

1 Answers1

0

It sounds like what you want are the Locations that are near params[:loc] and associated with Basics that match the term params[:q]. If that interpretation is wrong, all the rest of this will be wrong.

To do this purely in the database, you need to join the locations table using the conditions added by the near scope with the basics and services tables using the conditions added by pg_search_scope. Unfortunately, I haven't found any way to use pg_search_scope with arbitrary joins to other tables, so I don't know of a simple query-based solution to this. But if your @business and @term results are relatively small, you can just do the join in ruby. After your controller code:

@matching_businesses = @business & @term.map(&:location)

There are other ways to do that, but the idea is to find the unique set of Locations between those in your @business results and those associated with the Basics in your @term results. Here's another way that maybe reads more clearly:

@matching_businesses = @business.select do |business|
  @term.any? { |basic| business.basics.include?(basic) }
end

If that isn't performant enough, you'll probably need to write your own queries or read through the pg_search_scope implementation to figure out how to make it support arbitrary joins.

jsanders
  • 599
  • 5
  • 7