Disjunctions and Conjunctions are not working with inner join

[sunloverz]

I have the following models:

class Book
  has_many :authors  

  searchable do
    join(:job_id, :target => Author, :type => :integer, :join => { :from => :book_id, :to => :id })
    join(:score, :target => Author, :type => :double, :join => { :from => :book_id, :to => :id })
  end
end

class Author 
  belongs_to :book

  searchable do
    integer :book_id
    integer :job_id
    double :score
  end
end

But conjunction here is not working with join fields

Book.search do
  all_of do 
    with(:score).greater_than(10.0)
    with(:job_id, job_id)
  end
end

[mlh758]

@gr8bit this looks topical to you.

[gr8bit]

@mlh758 wtf, that's exactly what we were talking about yesterday. :O What a coincidence...

@sunloverz unfortunate news: I don't think this currently works. mlh785 and myself have been discussing this in #885 since yesterday because I had the same issue. The joins aren't merged. You'll find a hint on adjust_solr_params method you can use in your search to manipulate the solr query for now. I'll share my code when I'm done (should be later today).

[mlh758]

We should use this issue to continue the discussion since #885 was for something else originally and this is exactly on topic. I think this should be solvable but I haven't had time to look yet. I'd certainly advocate using adjust_solr_params in the mean time.

[sunloverz]

@gr8bit If you have a code could you please share it? I would appreciate greatly.

[gr8bit]

@sunloverz untested, will test tomorrow myself. ;) Seems to work fine for me... Written on/for Ruby 2.6+ and (probably, I didn't check the array methods) Rails.

    Book.search do
      adjust_solr_params do |params|
        new_fq = []
        params[:fq]
          .group_by { |field_query|
            # adapt this join query regexp to match only the joins you want to
            # merge or all compatible joins will be merged (default)
            field_query[/\A{!join from=.+? to=.+? v='type:".+?" AND /]
          }
          .each { |base_join, field_queries|
            # add non-matching field queries (key nil) and single occurrences
            # to new field queries directly
            new_fq.concat(field_queries) && next if !base_join || field_queries.length == 1
            # merge matched multiple join queries
            # 1. extract conditions from joins and combine into a single query
            conditions =
              field_queries.map { |join_query|
                cond = join_query[base_join.length..][/.*?(?=(?<!\\)')/]
                "( #{cond} )"
              }.join(" AND ")
            # 2. as the base_join is only a part of the join, add all
            # conditions to the first join, replacing its previous
            # condition.
            new_fq << base_join + field_queries.first[base_join.length..].sub(/\A.*?(?=(?<!\\)')/, conditions)
          }
        params[:fq] = new_fq
      end
      all_of do 
        with(:score).greater_than(10.0)
        with(:job_id, job_id)
      end
    end

[sunloverz]

@gr8bit Thank you very much for your code and explanation!

It generates the following sunspot request without AND keyword.
<Sunspot::Search:{:fq=>["type:Book", "{!join from=book_id_i to=id_i}score_e:{10\\.0 TO *}", "{!join from=book_id_i to=id_i}job_id_i:859"], :start=>0, :rows=>30, :q=>"*:*"}>

I guess it should generate a query like that? ({!join from=book_id_i to=id_i}job_id_i:859 AND {!join from=book_id_i to=id_i}score_e:{25\\.0 TO *})

[sunloverz]

I have decided to use the following code as a temporary solution. Thanks, guys!

   Book.search do
      adjust_solr_params do |params|
        params[:fq] = "{!join from=book_id_i to=id_i}job_id_i:859 AND score_e:{10.0 TO *}"
      end
    end

[gr8bit]

Hi @sunloverz, doesn't your query miss the type of the entries to join? Like type:"Author"? I think this query <Sunspot::Search:{:fq=>["type:Book", "{!join from=book_id_i to=id_i}score_e:{10\\.0 TO *}", "{!join from=book_id_i to=id_i}job_id_i:859"], :start=>0, :rows=>30, :q=>"*:*"}> might give you wrong results and currently only works because Author is the only entity in Solr that has a book_id...

Unfortunately my join queries look different and have a v "attribute" instead of a value after the join. wtf..?

Regarding your current workaround, it removes the type query - perhaps this might produce the results you want?

Book.search do
  adjust_solr_params do |params|
    params[:fq] = [params[:fq].first, "{!join from=book_id_i to=id_i}type:\"Author\" AND job_id_i:859 AND score_e:{10.0 TO *}"]
  end
end

[sunloverz]

Hi @gr8bit, I don't know why it behaves differently. I'm using the latest sunspot 2.5.0 version.

Thanks for the suggestion, it works great!