Thanks Pat, Hmmm yeah looks like this will be my only option. So in this case,I just create a new index for RoomDates, and don't need the MVA in the Rooms. I assume it'd be more efficient to change my RoomDate model to indexing unavailable RANGES rather than individual nights? Otherwise there could be hundreds of RoomDate records for every Room.
This was working in a previous version thought right? I had an app on TS 2, rails 3 with this exact functionality and it worked Your solution in that github discussion looks like the best (only) option in my case. Do you think this is going to give a noticeable performance hit with thousands of rooms / hundreds of thousands of RoomDates? Thanks for your help, much appreciated - loving TS apart from this! On Monday, September 16, 2013 11:10:42 AM UTC+1, Pat Allan wrote: > > 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] <javascript:>. > To post to this group, send email to [email protected]<javascript:> > . > 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.
