Hi Dave So, the OR issue is due to Sphinx not yet supporting OR in its SphinxQL WHERE clauses, which is certainly annoying.
An option for working around this could be searching on your RoomDate model instead? I actually had a very similar discussion on GitHub recently: https://github.com/pat/thinking-sphinx/issues/598 -- Pat On 15/09/2013, at 12:41 PM, Dave C wrote: > I'm indexing 'rooms' in my rails app, which has a multi-value attribute > column to store timestamps of all the 'unavailable dates' for each listing. > This appears to be working, with the following code > > has 'COALESCE((SELECT array_to_string(array_agg(CAST(extract(epoch FROM > "room_dates"."date") AS INT)), \',\') > FROM "room_dates" WHERE "room_dates"."room_id" = "rooms"."id" AND > "room_dates"."available" = FALSE > AND "room_dates"."date" BETWEEN (CURRENT_DATE - interval \'7 days\') AND > (CURRENT_DATE + interval \'6 months\')), \'0\')', > :as => :unavailable_dates, :type => :integer, :multi => true > > When I add the line > > @ts_params[:with][:unavailable_dates] = (check_in.to_i)..(check_out.to_i) > > to my search controller, it correctly finds the rooms where one of the > unavailable dates is between the search dates. But when I try to do the > opposite, ie use ":without" instead of ":with" (which is what I need to do), > it gives an error "sphinxql: syntax error, unexpected OR, expecting $end near > 'OR unavailable_dates > 1381276800 ORDER BY `updated_at` DESC LIMIT 0, 5; > SHOW META'" . The full generated Sphinx query is: > > Sphinx Query (1.2ms) SELECT * FROM `room_core` WHERE `min_stay` BETWEEN > 1 AND 21 AND `max_stay` BETWEEN 21 AND 1000 AND `sphinx_deleted` = 0 AND > `unavailable_dates` < 1379462400 OR unavailable_dates > 1381276800 ORDER BY > `updated_at` DESC LIMIT 0, 5 > > Any idea where I'm going wrong, or could this be a bug? > > One other question, would there be any way to filter out the rooms only if > more than a certain percentage of the search dates are unavailable? Is this > even possible with thinking sphinx? For example if a user searches for a > specific 2 week period, and one of the listings is unavailable for 2 of those > nights, I still want to display it. > > Thanks! > > > -- > You received this message because you are subscribed to the Google Groups > "Thinking Sphinx" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/thinking-sphinx. > For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/thinking-sphinx. For more options, visit https://groups.google.com/groups/opt_out.
