On Friday, November 8, 2013 12:08:25 PM UTC-8, Snarke wrote:
> I'm staring at the last section of the "Advanced Associations"
> documentation, which says "you can use Sequel‘s association support to get
> aggregate information for columns in associated tables (sums, averages,
> etc.)"
>
> Yup, that's what I want to do. But the provided example is absolutely
> baffling me. Truncated code for my case as follows:
>
> TABLE skus (
> id uuid DEFAULT uuid_generate_v1() NOT NULL,
> name varchar,
> PRIMARY KEY (id)
> );
>
> TABLE items (
> id uuid DEFAULT uuid_generate_v1() NOT NULL,
> sku_id uuid NOT NULL,
> quantity int4,
> PRIMARY KEY (id)
> );
>
> An "item" is an invoice line item. If one row of the 'skus' table is for a
> brown chair, there will be 0 or more rows in 'items' that indicate I have
> sold/shipped various quantities of brown chairs to some number of people.
> There's the obvious foreign key between sku_id and skus.id.
>
> class Sku < Sequel::Model
> one_to_many :items
> end
>
> class Item < Sequel::Model
> many_to_one :sku
> end
>
> What I'd like to do is be able to retrieve a set of skus and the sum of
> the quantity field of the associated items. In other words "Sku A, and I've
> shipped xx of them. Sku B, and I've shipped yy of them," et cetera. I can
> certainly do that in ways that require lots of queries, but I can't quite
> wrap my head around how create the Sequel equivalent of
>
> select skus.*, sum(items.quantity)
> from skus join items on (skus.id=sku_id)
> group by skus.*
>
> which isn't actually valid SQL code, I know. It could also be
>
> select *,
> (select sum(quantity) from items where sku_id = skus.id)
> from skus
>
The latter approach is fairly easy to do if you just want to use a single
query:
Sku.select_append(Item.select{sum(:quantity)}.where(:sku_id=>:skus__id).as(:quantity)).all
The join and group by approach is possible as well, but more work.
The eager loader in the Advanced Associations example looks pretty similar
to what you want, here's an untested version:
class Sku < Sequel::Model
one_to_many :items
many_to_one :item_quantities, :read_only=>true, :key=>:id,
:dataset=>proc{Item.where(:sku_id=>id).select{sum(quantity).as(quantity)}},
:eager_loader=>(proc do |eo|
eo[:rows].each{|p| p.associations[:item_quantities] = nil}
Item.where(:sku_id=>eo[:id_map].keys).
select_group(:sku_id).
select_append{sum(quantity).as(quantity)}.
all do |t|
p = eo[:id_map][t.values.delete(:sku_id)].first
p.associations[:item_quantities] = t
end
end)
def item_quantities
if s = super
s[:quantity]
end
end
end
class Item < Sequel::Model
many_to_one :sku
end
Note that this is different than your SQL queries, as it only returns the
sum of the quantities, not the sum of the quantities plus the object's
values. The associations code allows you to do:
sku = Sku[1]
sku.item_quantities # Sum of quantity for this sku's items in 1 query
The eager loader makes it so:
Sku.eager(:item_quantities).all # 2 queries
Sku.map{|s| s.item_quantities} # 0 queries
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/groups/opt_out.
