Thinking sphinx index with Multiple joins and conditions

Question

I have 3 models : Users has_one Service and Users Has_many Phones (verified or not, destroyed or not)

Class Phone < ActiveRecord::Base
  belongs_to :user
end

class User < ActiveRecord::Base
     has_one :service
     has_many :phones
     has_many :verified_phones, -> {where('verified_at IS NOT NULL AND destroyed_at IS NULL')}, class_name: 'Phone'
end

class Service < ActiveRecord::Base
    belongs_to :user
    has_many :phones, through: :user
    has_many :verified_phones, through: :user
end

I'd like to define an TS-index on the model Service. I want an boolean-faceted attribute which represents Service whose user has one or more verified phone.

So after reading this post I tried :

join verified_phones
has 'COUNT(verified_phones.id) > 0', as: :user_phone_verified, type: :boolean, facet: true

but send me the error "Unknown column 'verified_phones.id' in 'field list' " (the same error occured in the post)

Then I tried

join verified_phones
has 'COUNT(phones.id) > 0', as: :user_phone_verified, type: :boolean, facet: true
-> but result are wrong : attribute is true for every user that have a phone, verified or not, destroyed or not.

Then I tried

join phones
has 'COUNT(phones.verified_at IS NOT NULL AND phones.destroyed_at IS NULL) > 0', as: :user_phone_verified, type: :boolean, facet: true

-> same problem : attribute is true for every user that have a phone, verified or not, destroyed or not.

I'm not good with SQL syntax, could anybody help me to solve this ? Thanks

Answer 1

The second approach is what you're after - even though you're joining on the special association, the table alias will be the table name by default - hence, phones.id.

That said, what's being returned doesn't seem to be right. Perhaps the following instead:

join verified_phones
has "SUM(CASE WHEN phones.id IS NULL THEN 0 ELSE 1 END) > 0",
  as: :user_phone_verified, type: :boolean, facet: true

Answer 2

I got it : I should have been more precise with the syntax of my association definition :

has_many :verified_phones, -> {where('verified_at IS NOT NULL AND destroyed_at IS NULL')}, class_name: 'Phone'

should have been:

has_many :verified_phones, -> {where('phones.verified_at IS NOT NULL AND phones.destroyed_at IS NULL')}, class_name: 'Phone'

My User model has the same attribute "destroyed_at" -> the generated SQL was looking this attribute in the wrong table.

So with this correction both syntax work:

my initial :

join verified_phones
has 'COUNT(phones.id) > 0', as: :user_phone_verified, type: :boolean, facet: true

or the suggested by @Pat :

    has "SUM(CASE WHEN phones.id IS NULL THEN 0 ELSE 1 END) > 0",
  as: :user_phone_verified, type: :boolean, facet: true

But I'm not very confortable with this because I see possible confusion with this table alias still named "phones". What happen if I need to join in the same index phones and verified_phones?

But for now it solve my problem, so thank you @Pat !