Allowing conditions with nil?

[Coilcore]

I have a use case where I need to get records which have a field with
a specific value or that field is blank. Its almost like an inverted,
exclusion case, but not really.

So as an example if I'm looking for items with :available_color =>
'green', I want to get back records that have the
field :available_color set to 'green' mixed with rows that have
no :available_color at all and have them come back in our (already
working) weighted order.

I've tried the following:

:conditions => { :available_color => [nil, 'green'] }
:conditions => { :available_color => ['', 'green'] }

Which give me back items with :available_color => green ONLY. I've
tried using both :with and :without, but those act oddly because they
are supposed to be against attributes, not fields.

I could even do something if I have a

:not_conditions => { :available_color => available_colors -
['green'] }

Anyone have any interesting or clever ways to do this? It could even
be via some complicated indexed field as long as I don't change the
mysql schema.

[Pat Allan]

Hi Coilcore

I don't think it's possible to do what you want to do. When specifying
certain fields, Thinking Sphinx uses the extended search syntax.
http://sphinxsearch.com/doc.html#extended-syntax

So :conditions => {:available_color => 'green'} gets converted to
"@available_color green"

As far as I can tell, there doesn't seem to be a way to specify a
field being empty. Personally, I'd recommend adding in a foreign key
for the colour, and then you could use :with => {:available_color_id
=> [0, 12]}, where 12 is green - nils/NULLs are treated as 0 by Sphinx.

But since you don't want schema changes, the only way I could think of
making this work is by listing all other colours and saying you
*don't* want them:
Model.search "@available_color -(blue|red|yellow)"

Not really a neat solution, though - although you do have the
available_colors array.. so perhaps worth a shot?

--
Pat

[Coilcore]

Certainly some interesting directions to go.

I really like the idea of using the id, but in this case its a many-to-
many relationship (join table and habtm association), and I don't
think sphinx supports multiple values in an attribute. So close but
doesn't quite work.

My two current angles of attack are:

1) Adding a magic keyword, which is a bit crude but will work if my
search includes the same magic keyword.. e.g. @available_colors
(__COLORLESS__|green). I'll probably do this by adding
a :null_keyword to the field object and an :include_null to the search
object.

2) Alternatively adding a available_color_count attribute. Though
I'm not sure this will work because I don't know how the query would
look. I don't think I can do '@available_colors green |
@available_color_count 0', since as an attribute its gotta be done as
a filter.

Working on the first option now. ;)

On Aug 6, 1:54 am, Pat Allan <p...@freelancing-gods.com> wrote:
> Hi Coilcore
>
> I don't think it's possible to do what you want to do. When specifying  

> certain fields, Thinking Sphinx uses the extended search syntax.http://sphinxsearch.com/doc.html#extended-syntax

[Coilcore]

Oh, I experimented with the syntax of

Model.search "@available_color -(blue|red|yellow)"

Because there could be multiple available colors, it doesn't return
the results needed (e.g. records with available colors of 'green' or
'blue' do not show up).

On Aug 6, 1:54 am, Pat Allan <p...@freelancing-gods.com> wrote:

> Hi Coilcore
>
> I don't think it's possible to do what you want to do. When specifying  

> certain fields, Thinking Sphinx uses the extended search syntax.http://sphinxsearch.com/doc.html#extended-syntax

[Pat Allan]

> I really like the idea of using the id, but in this case its a many-
> to-
> many relationship (join table and habtm association), and I don't
> think sphinx supports multiple values in an attribute. So close but
> doesn't quite work.

Sphinx does support multiple-value-attributes (MVAs) - for integers
only though (although date-times and probably booleans are treated as
ints anyway). So you should be able to create an attribute something
like the following:
has available_colours(:id), :as => :available_colour_ids

[Coilcore]

I have a working version using my little :null_value hack. Its a very
small change to the code, but I still don't like having to use a magic
value in my case '__NULL__'.

I tried converting to using an attribute using the ids as you
suggested, because it seems cleaner. If I run the sql from the config
file it looks like it should work, but when I remove my :condition for
magic value hack and replace it with a :with using the ids it seems to
mostly work but my sort order disappears (it starts getting results
order by id rather than @relevance). Totally not sure why...

Using this comes back sorted by relevance with appropriate weights:

search_options[:conditions] = {:available_colors =>
"(#{NULL_KEYWORD}|#{colors.map(&:name).join('|')})" }

Using this seems to get the same results with 'default' sort order:

search_options[:with]['available_colors'] = [0]+colors.map(&:id)

I have the two lines side by side, and comment one out then alternate,
and the weights come and go as described. Weird.

Well I have something that works for now. So at least I can move
forward. ;)

[Pat Allan]

Hmm, it is odd that the sort order changes - but as you've said, at
least you've found a working solution anyway :)

--
Pat