searchlogic with dynamic association scopes + will_paginate = bad sql

[aepstein]

Picture something like this:

/app/models/location.rb:

class Location < ActiveRecord::Base
  has_many :shifts
end

/app/models/shift.rb

class Shift < ActiveRecord::Base
  belongs_to :location
end

/app/models/assignment.rb

class Assignment < ActiveRecord::Base
  belongs_to :shift
end

Then, try something like this in a controller:

@assignments = Assignment.shift_location_name_like('central').paginate( :page => params[:page], :include => [ :shift ] )

This produces sql that has both the INNER JOIN from the searchlogic generator and the LEFT OUTER JOIN from the include. From documentation, it appears that using include in the find method should prevent the INNER JOIN sql from being generated. If I use the find method directly instead of paginate, the sql generates correctly (minus, of course the pagination magic).

[danielricecodes]

I was able to use script\console to find a workaround (or the solution if this is how I am supposed to use this gem!).

First step: capture the Searchlogic::Search object to its own variable

    >> s = Timeentry.notes_content_like("search").search
    => #<Searchlogic::Search:0x25bbc2c @current_scope={:conditions=>["notes.content LIKE ?", "%search%"], :readonly=>true, :joins=>:notes}, @conditions={}, @klass=Timeentry(id: integer, job_id: integer, record_date: date, travel_time: decimal, client_time: decimal, created_at: datetime, updated_at: datetime, user_id: integer, created_by_user_id: integer, updated_by_user_id: integer)>

Then manually update the :joins key/value pair in current_scope:

   >> s.current_scope[:joins] = 'LEFT OUTER JOIN `notes` ON `notes`.noteable_id = `timeentries`.id AND `notes`.noteable_type = "Timeentry"'
   => "LEFT OUTER JOIN `notes` ON `notes`.noteable_id = `timeentries`.id AND `notes`.noteable_type = \"Timeentry\""

Then execute the search using all, paginate, or whatever you wish:

    >> s.all(:select=>'DISTINCT `timeentries`.*')

   => []

The sql that gets generated is now correct.

       SELECT DISTINCT `timeentries`.* FROM `timeentries` LEFT OUTER JOIN `notes` ON `notes`.noteable_id = `timeentries`.id AND `notes`.noteable_type = "Timeentry" WHERE (notes.content LIKE '%search%')

And for anyone reading this, here is how I implemented it in my search controller:

@search = Timeentry.id_eq_or_travel_time_eq_or_client_time_eq_or_notes_content_like(params[:quick_search][:text].to_s).search()
@search.current_scope[:joins] = 'LEFT OUTER JOIN `notes` ON `notes`.noteable_id = `timeentries`.id AND `notes`.noteable_type = "Timeentry"'
@timeentries = @search.paginate(:page => params[:page], :select=>'DISTINCT `timeentries`.*')