Using tsrange looks really promising, thanks for the suggestion! One
question on sequel syntax: what's the correct form to set +infinity as the
end time?
On Monday, June 15, 2015 at 4:00:34 PM UTC-4, Jeremy Evans wrote:
>
> On Monday, June 15, 2015 at 12:40:01 PM UTC-7, Andrew Burleson wrote:
>>
>> Yes this is more of a SQL question (sorry, unfortunately my ruby fu
>> greatly exceeds my SQL fu).
>>
>> And yes, "Basically, you are looking for orders where the last entry
>> before the given time in the order_states table had a state of active," and
>> I don't really know how to set that up in SQL.
>>
>> Digging more on your denormalization suggestion:
>>
>> The key here for me is that customers can toggle orders on and off more
>> than once, think of a subscription that can be paused at any time, and I
>> want to be able to see for a given point in time which orders were active.
>> If I have an active_at and inactive_at column those would work great if the
>> order could only be one of those two, but since it can toggle I figured an
>> `OrderStates` table was the best way to accomplish this.
>>
>> Is there another different / better way to do this kind of thing?
>>
>
> With PostgreSQL, a separate table containing a foreign_key to orders and a
> tsrange column is probably the best way. You can add a gist index on the
> tsrange column, and find matching orders:
>
> DB.create_table(:order_active){foreign_key :order_id, :orders; tsrange
> :active, :index=>{:type=>:gist}}
>
> Order.def_dataset_method(:active_at) do |time|
>
> where(:id=>DB[:order_active].select(:order_id).where(Sequel.pg_range(:active).contains(time)))
> end
>
> The above requires the pg_range_ops extension, which ships with Sequel.
>
> As before, the example given is untested, but hopefully it can at least
> point you in the right direction.
>
> Thanks,
> Jeremy
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" 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/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
