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.
