Thanks Timo, much appreciated, I got it working with:
excluded = ThinkingSphinx.search(
classes: [RoomDate],
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["@count"]*1.0) / 14 > 0.4
}.compact
On Tuesday, October 1, 2013 6:02:09 AM UTC+1, Timo Virkkala wrote:
>
> Hi Dave,
>
> I think I can answer that - Pat will probably confirm this.
>
> I noticed a couple of days ago that :middleware is not available (or does
> not work) on searches made by <model>.search() calls. I think you'll get
> this code to work if you change it to ThinkingSphinx.search() and add
> :classes => [RoomDate].
>
> Also, you'll probably also need to change that to 'if r["num_days"] > 3',
> since the RAW_ONLY middleware stack returns hashes with strings as keys.
>
> -T-
>
>
>
>
> On 1 October 2013 04:34, Dave C <[email protected] <javascript:>>wrote:
>
>> 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]> 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<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 thinking-sphi...@googlegroups.**com.
>>>> To post to this group, send email to [email protected].
>>>> Visit this group at
>>>> http://groups.google.com/**group/thinking-sphinx<http://groups.google.com/group/thinking-sphinx>
>>>> .
>>>> For more options, visit
>>>> https://groups.google.com/**groups/opt_out<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 thinking-sphi...@**googlegroups.com.
>>> To post to this group, send email to thinkin...@googlegroups.**com.
>>> Visit this group at
>>> http://groups.google.com/**group/thinking-sphinx<http://groups.google.com/group/thinking-sphinx>
>>> .
>>> For more options, visit
>>> https://groups.google.com/**groups/opt_out<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.
