Hi Dave This is one (perhaps the only) piece of functionality that was more flexible with Sphinx's binary protocol. TS v3 uses Sphinx's MySQL/SphinxQL protocol, which is very similar to SQL, but as discussed, no support for OR makes it a little frustrating in these edge cases.
>From a performance perspective, provided you use the RAW_ONLY middleware stack >(thus avoiding unnecessary ActiveRecord object initialisation) I think it >shouldn't be too much of a hit. But you're in a much better position to >measure that than I am! Hope things are working smoothly for you. -- Pat On 17/09/2013, at 2:08 AM, Dave C <[email protected]> wrote: > 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]. >> 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. -- 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.
