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.
