filtering on null attributes

[Minh Tran]

Hi,

I have a problem where I need to filter on foreign keys, keeping nulls
and specific ones.
For example, say I have a Car model with an owner_id that may be null
(ie car is unowned),
and the following index:

define_index do
...
has owner_id, :as => :owner
end

To filter for unowned and my cars, I wanted to be able to do something
like

Car.search :with => { :owner => [ nil, 1 ] } # does not work

However, my understanding of sphinx (correct me if I'm wrong) is that
it cannot search on
null attributes, so I would have to replace null values (e.g. with
'0') and search for that value
instead. I've gotten mild success by modifying the SQL that TS
generates by wrapping
IFNULL(...) around columns of interest.

If this is the de facto standard work-around, may I propose adding to
the plugin an option :null
that would replace null values with a specified value. For instance,

define_index do
...
has owner_id, { :as => :owner, :null => '0' } # replaces null with
'0'
end

If I am going about this all wrong, any feedback would be greatly
appreciated.

Thanks for the time,
Minh

[Pat Allan]

Hi Minh

I think Sphinx stores NULLs as 0's - so without patching, hopefully
the following will work:
Car.search :with => {:owner => [0, 1]}

Otherwise, we might need such a workaround like you're proposing -
would definitely be interested in seeing your code changes :)

Cheers

--
Pat
e: p...@freelancing-gods.com || m: +614 1327 3337
w: http://freelancing-gods.com || t: twitter.com/pat
discworld: http://ausdwcon.org || skype: patallan

[Minh Tran]

Hi Pat,

Sorry for not following up sooner. I've been busy, but I guess that
can be said about all of us. Regardless, I've been following google
groups, and my problem and temporary solution is similar to that
discussed in this thread:

http://groups.google.com/group/thinking-sphinx/browse_thread/thread/8a2a9decc08444fd?hl=en

Also, you're right about sphinx storing nulls as 0's for single-value
attributes. It turns out that the example I gave for explanation sake
was oversimplified; I was actually dealing with multi-valued
attributes, the default of which are empty lists (at least I'm led to
believe so from the command-line search tool bundled with sphinx).
Anyway, the solution I employed is much like Coilcore mentioned in the
other thread, as I couldn't tell sphinx to search for records with
empty arrays.

Thank you for all your work with sphinx and your speedy reply last
time,
Minh

[Pat Allan]

Hi Minh

Good to know you've found a solution :)

Cheers

--
Pat