On Tuesday, July 31, 2012 12:53:48 AM UTC-7, Snarke wrote:
>
> I can't quite wrap my brain around the following situation.
>
> I have an invoice, which is a row in the table Orders.
> There are multiple items on the invoice, which are rows in table Items
> that are keyed to the order.
> I want to figure out how much this order weighs.
>
> Weights for items are stored in the table SKUs. Each item points back to a
> particular SKU.
>
>
> In Sequel, I started out by trying to do something like
>
> Orders[2543].items.skus.sum(:weight)
>
> but of course that doesn't work. Then I started fiddling with
>
> Orders[2543].items_dataset . . .
>
> but reading through the documentation on associations didn't give me any
> idea where to go next. I suspect if I go back to the docs for datasets I
> might figure it out. However, when I start to feel like I'm just writing a
> flippin' SQL query, except I'm doing it in a foreign language, that's when
> I wonder if I've missed something.
>
Well, if you use the dataset_associations plugin, you could probably do:
Orders[2543].items_dataset.skus.sum(:weight)
FWIW, Sequel's DSL is basically a way to write an SQL query using ruby.
This can be good or bad depending on the situation. If you don't need to
support multiple databases, there is no problem with just writing SQL,
Sequel supports that just fine.
Sure, I can just shove all the model crap out of the way and reach right
> into the database and make it tell me what I want to know:
> DB['select sum(skus.weight) from orders join items on (orders.id=
> order_id) join skus on (sku_id =
> skus.id) where orders.id = 2543'].first
>
If you know the SQL you want, then just using it is not a problem. You
could also express such a query using Sequel's DSL:
DB[:orders].
join(:items, :order_id=>:id).
join(:skus, :id=>:sku_id).
where(:orders__id=>2543).
sum(:skus__weight)
The difference between this query and the above using dataset_associations
is that this one does a single query with two joins, while the dataset
associations one uses 2 queries (first one to get the order), with the
second query using a subselect. Note that you don't appear to need to join
to orders, so the query is probably better written as:
DB[:items].
join(:skus, :id=>:sku_id, 2543=>:order_id).
sum(:skus__weight)
So that's a single query with a single join.
But I really like models. I just haven't figured out how to reach through a
> model's associations to the associations beyond that except by bypassing
> the whole thing and using SQL.
Models are just designed to be a thin wrapper around a dataset and
individual dataset rows that adds some nice behavior. For what you are
doing (aggregations), they aren't that helpful beyond a place to store
methods. For example, I would probably implement what you want as an
instance method on Order:
class Order
def weight
DB[:items].join(:skus, :id=>:sku_id, id=>:order_id).sum(:skus__weight)
end
end
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sequel-talk/-/lYfNcTwRo4AJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
