Hi Pat,
Just following up on this - I've almost got it working, but not quite. I
have the following:
ur = RoomDate.search(
select: "count(*) as num_days",
with: {unavailable_date: check_in..check_out},
group_by: :room_id,
middleware: ThinkingSphinx::Middlewares::RAW_ONLY
).collect { |r| r.room_id if r.num_days > 3 }
.compact
But it gives me an 'undefined method ' num_days' error. When I look in the
console, it seems to be creating activerecord objects despite the
'middleware' bit, so not surprisingly the 'num_days' attribute isn't
available in the 'RoomDate.load' query
How do I stop it from loading the activerecord objects, so I can use the
'num_days' attribute in a conditional as above?
The idea is basically just to grab all the listings which are unavailable
for more than X days during the search dates. Then these are removed from
the main query.
Thanks so much for your help!
On Sunday, September 22, 2013 10:39:01 AM UTC+1, Pat Allan wrote:
>
> 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] <javascript:>>
> 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] <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.
