Different conditions for different fields in thinking-sphinx

Question

I have:

ThinkingSphinx::Index.define :new_post, with: :real_time do
  indexes title, sortable: true
  indexes text

  has state, type: :string
  has forum_hidden, type: :boolean
  has created_at, type: :timestamp
  has publish_at, type: :timestamp
  has reminde_at, type: :timestamp
  has deleted_at, type: :timestamp
  has content_category_ids, type: :integer, multi: true
end

And let's i need to get all the records where @title=query with any value publish_at or @text=query with publish_at = 1.month.ago..Time.current

That is, I need to combine these two requests:

NewPost.search(conditions: { title: query })
NewPost.search(conditions: { text: query }, with: { publish_at: 1.month.ago..Time.current })

The result is needed with excerpts

UPDATE

published_at interval for the @title and @text fields is always different and depends on the user's rights. For example, there can be such a situation:

NewPost.search(conditions: { title: query }, with: { publish_at: 1.year.ago..Time.current })
NewPost.search(conditions: { text: query }, with: { publish_at: 6.month.ago..Time.current })

and all results that do not fall under these conditions should not be displayed at all

Having now seen the edit, you probably have no choice than to just run two queries, and merge... doubt ts has a union feature — barryhunter

barryhunter barryhunter · Accepted Answer · 2017-10-18T17:15:52

The main thing when wanting to 'combine' multiple criteria is deciding on a 'calculation' how to compute a weight that gives that effect. Sphinx computes a weight and orders by that. Rather than unions of multiple distinct queries

For example https://freelancing-gods.com/thinking-sphinx/searching.html#sorting

ThinkingSphinx.search(
  :select => '*, WEIGHT() + IF(publish_at>NOW()-2592000,1000,0) AS custom_weight',
  :order  => 'custom_weight DESC'
)

would use a the 'full-text' search weight, but add 1000 if in last month.

Uses the sphinx NOW() function http://sphinxsearch.com/docs/current.html#expr-func-now

You might also want field weights https://freelancing-gods.com/thinking-sphinx/searching.html#fieldweights to boost title matches over 'text' matches

:field_weights => { :title=>10 }

Bringing all togehter (if got the ruby syntax right... )

NewPost.search( query , 
  :select => '*, weight() + IF(publish_at>NOW()-2592000,1000,0) as custom_weight',
  :order  => 'custom_weight DESC', 
  :field_weights => { :title=>10 }
)

... in theory title matches will be first. And recent ones towards start too. Not EXACTLY what you asked for, but close.

Different conditions for different fields in thinking-sphinx

1 Answers