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?
Thanks!
Andrew
On Monday, June 15, 2015 at 3:30:43 PM UTC-4, Jeremy Evans wrote:
>
> On Monday, June 15, 2015 at 11:27:01 AM UTC-7, Andrew Burleson wrote:
>>
>> I've got two models like this:
>>
>> class Order < Sequel::Model
>> has_many :order_states
>> end
>>
>>
>> class OrderState < Sequel::Model
>>
>> set_dataset dataset.order(:created_at).reverse
>> many_to_one :order
>> end
>>
>>
>> An Order can be `active` or `inactive`, users can toggle this. I've added
>> an after save hook that creates a new order state if the state was just
>> changed, like this:
>>
>> def after_save
>> super
>> add_order_state(state: state) if order_states.last.try(:state) != state
>> end
>>
>> This all works great in terms of recording the state changes on the order
>> over time.
>>
>
> If you can, I'd recommend using a database trigger for this, it's a more
> robust approach.
>
>
>> I need to write a dataset_module method `active_at(time)` that can tell
>> me if the service order was active at a particular time. The part that is
>> tripping me up is, I need to query against the joined `order_states` table,
>> then for each `order_state` check the last order state prior to the given
>> time and see then select the order if that order_state was active.
>>
>> If I was looking at a single record I might do something like:
>>
>> order.order_states.order(:created_at).reverse.where{ created_at < time }.
>> last.state == "active"
>>
>> I haven't been able to think of how to write that as a dataset query
>> method though. How would you do that?
>>
>
> This is more of an SQL question than a Sequel question, unless you know
> the SQL for the query you want and just aren't sure how to express it in
> Sequel syntax. If that's the case, it is best to post the SQL you want to
> use.
>
> Without actually testing it, I'm guessing something like:
>
> Order.def_dataset_method(:active_at) do |time|
> ds = OrderState.where(OrderState.
> from(:order_states___os).
> select(:id).
> where{created_at < time}.
> where(:os__order_id=>:order_states__order_id).
> reverse(:created_at).
> limit(1)=>:id)
> where(['active', :id]=>ds.select(:state, :order_id))
> end
>
> Which will be compiled into something like:
>
> SELECT * FROM orders
> WHERE (('active', id) IN (
> SELECT state, order_id IN order_states
> WHERE (
> SELECT id FROM order_states AS os
> WHERE created_at < ? AND os.order_id = order_states.order_id
> ORDER BY created_at DESC
> LIMIT 1
> ) = id)
> )
>
> Basically, you are looking for orders where the last entry before the
> given time in the order_states table had a state of active.
>
> I wouldn't expect this query to perform well on a large dataset. To
> optimize it, you'd want to denormalize and add a inactive_at column to the
> orders table, containing the timestamp when the order was no longer active
> (also an active_at column if active is not the initial state, for when the
> order became active). With an index on that column, it would be fairly
> fast to filter by whether the order was active at a given time.
>
> 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.
