CODEWITHSUNDEEP
ruby-on-railsthinking-sphinx
prograils
prograils

Reputation: 2376

ThinkingSphinx: dynamic indices on the SQL-backed indices?

I am trying to use ThinkingSphinx (with SQL-backed indices) in my Rails 5 project.

I need some dynamic run-time indices to search over.

I have a Message model:

class Message < ApplicationRecord
    belongs_to :sender, class_name: 'User', :inverse_of => :messages
    belongs_to :recipient, class_name: 'User', :inverse_of => :messages
end

and its indexer:

ThinkingSphinx::Index.define :message, :with => :active_record, :delta => true do
    indexes text

    indexes sender.email, :as => :sender_email, :sortable => true

    indexes recipient.email, :as => :recipient_email, :sortable => true 

    indexes [sender.email, recipient.email], :as => :messager_email, :sortable => true

    has sender_id, created_at, updated_at

    has recipient_id

end

schema.rb:

  create_table "messages", force: :cascade do |t|
    t.integer  "sender_id"
    t.integer  "recipient_id"
    t.text     "text"
    t.datetime "created_at",                   null: false
    t.datetime "updated_at",                   null: false
    t.boolean  "read",         default: false
    t.boolean  "spam",         default: false
    t.boolean  "delta",        default: true,  null: false
    t.index ["recipient_id"], name: "index_messages_on_recipient_id", using: :btree
    t.index ["sender_id"], name: "index_messages_on_sender_id", using: :btree
  end

The problem is about so-called "dialogs". They don't exist in the database - they are determined at run-time. A dialog - that's a set of messages between 2 users, where each user may be either a sender or a receiver.

The task is to search through my dialogs and to find the dialog (dialog's messages) by the piece of the correspondent email. So complicated!

Here's my effort:

  conditions = {messager_email: search_email}

  with_current_user_dialogs = 

  "*, IF(sender_id = #{current_user.id} OR recipient_id = #{current_user.id}, 1, 0) AS current_user_dialogs"

  messages = Message.search search_email, conditions: conditions,

    select: with_current_user_dialogs,
    with: {'current_user_dialogs' => 1}

This is almost fine - but still not. This query correctly searches only within my dialog (within the messages I sent or received) and only within :sender and :recipient fields simultaneously (which is not best).

Say my email is "[email protected]". Other emails are like "[email protected]", "[email protected]", "[email protected]".

The trouble is that when I search for "client1" - I get all the messages where I was either a sender or a receiver. But I should get nothing in response - I need to search only across my correspondents emails - not mine.

Even worse stuff happens also while querying "client" - I get back the correct correspondents with "[email protected]", "[email protected]" - but the result is spoiled with wrong "[email protected]".

I need a way to choose at run-time - which index subset to search within.

I mean this condition is not enough for me:

indexes [sender.email, recipient.email], :as => :messager_email, :sortable => true

It searches (for "client") within all the sender.email and all the recipient.email at once.

But I need to dynamically choose like: "search only within sender.email values conforming to if sender.id != current_user.id" OR "search only within recipient.email conforming to if recipient.id != current_user.id" (because I can be as a sender as a receiver).

That's what I call a "dynamic index".

How to do that? Such "dynamic index" surely would depend on the current current_user value - so it will be different for the different users - even on the same total messages set.

It is clear that I can't apply whatever post-search cut-offs (what to cut off?) - I need to somehow limitate the search itself.

I tried to search over some scope - but got the error that "searching is impossible over scopes" - something like that.

Maybe I should use the real-time indexing instead of the SQL-backed indexing?

Sorry for the complexity of my question.

Upvotes: 0

Views: 58

Answers (1)

pat
pat

Reputation: 16226

Would the following work?

other = User.find_by :email => search_email
with_current_user_dialogs = "*, IF((sender_id = #{current_user.id} AND recipient_id = #{other.id}) OR (recipient_id = #{current_user.id} AND sender_id = #{other.id}), 1, 0) AS current_user_dialogs"

Or do you need partial matches on the searched email address?

[EDIT]

Okay, from the discussion in the comments below, it's clear that the field data is critical. While you can construct a search query that uses both fields and attributes, you can't have logic in the query that combines the two. That is, you can't say: "Search field 'x' when attribute 'i' is 1, otherwise search field 'y'."

The only way I can possibly see this working is if you're using fields for both parts of the logic. Perhaps something like the following:

current_user_email = "\"" + current_user.email + "\""

Message.search(
  "(@sender_email #{current_user_email} @recipient_email #{search_email}) | (@sender_email #{search_email} @recipient_email #{current_user_email})"
)

Upvotes: 1

Related Questions