需要有关连接表的帮助,将结果限制为仅资源 ID [英] Need help on join table, limiting results to only the resource ID
问题描述
嘿,我有一个使用 3 个模型的代码块...
class 采购员 <ActiveRecord::Basehas_many :transhas_many :sellers, :through =>:trans验证:seller_id,存在:真结尾类卖家:trans验证:buyer_id,存在:真结尾类 Tran <ActiveRecord::Base归属地:买家验证:buyer_id,存在:真归属地:卖家验证:seller_id,存在:真结尾
供应商 - has_many trans,has_many 通过 trans 的买家
Trans - 归属于买家,归属于卖家
在我的seller_show 视图中,我可以执行以下代码并成功地给我一个卖家的买家列表,以及他们所有交易的总和.这一切都很好但是总和显示了所有买方的交易,不限于与卖方的交易.
我尝试了多种不同的方法来尝试限制seller_id = trans.supplier_id(外键)的交易,但似乎无法使其工作.
有人可以帮忙吗?预先非常感谢您!
(仅供参考,'sum' 是 trans 表中托管交易金额的列的标题令人困惑!)
<% @seller.buyers.uniq{|t|t.buyer_id }.sort_by {|su|su.trans.sum(:sum)}.reverse.each 做 |su|%><li><%=su.name%><%=su.trans.sum(:sum)%></li><%结束%></ol>
我认为有 2 种方法可以做到这一点.更简单的方法(尽管排序效率较低)是按 buyer_id
汇总总和,然后将其注入买家列表:
<%buyer_sums = @seller.trans.group(:buyer_id).sum(:sum)%><%buyer.where(id:buyer_sums.keys).sort_by {|b|-buyer_sums[b.id]}.each do |buyer|%><%=买家姓名%><%=buyer_sums[buyer.id]%><%结束%>
第二种方法是与买家一起提取金额:
<% Buyer.joins(:trans).merge(@seller.trans).select("buyers.*, SUM(`sum`) sums").order('SUM(`sum`)').each do |buyer|%><%=买家姓名%><%=buyer.sums %><%结束%>
第二种方法的优点是在检索整个集合后无需进行排序,这对可伸缩性很重要,尤其是与分页相关的.
请注意,此处使用的反引号 (`) 转义字符用于 MySQL,但如果不是,则应替换为适当的数据库约定(例如,PostgreSQL 的双引号).或者,使用 quote_column_name
方法来更智能地执行此操作:)
Hey all I have a block of code that utilises 3 models...
class Buyer < ActiveRecord::Base
has_many :trans
has_many :sellers, :through => :trans
validates :seller_id, presence: true
end
class Seller < ActiveRecord::Base
has_many :trans
has_many :buyers, :through => :trans
validates :buyer_id, presence: true
end
class Tran < ActiveRecord::Base
belongs_to :buyer
validates :buyer_id, presence: true
belongs_to :seller
validates :seller_id, presence: true
end
Suppliers - has_many trans, has_many buyers through trans
Trans - belongs_to buyers, belongs_to sellers
In my seller_show view I can execute the following code and successfully give me a list of the seller's Buyers, with a sum of all their transactions. That's all good however the sum shows all the buyer's transactions, not limited to the ones with the sellers.
I've tried loads of different ways to try and limit the transactions where the seller_id = trans.supplier_id (foreign key), but can't seem to make it work.
Can anyone help? Thank you very much in advance!
(FYI, 'sum' is confusingly the title of the column hosting the transaction amount in the trans table!)
<ol>
<% @seller.buyers.uniq{|t| t.buyer_id }.sort_by {|su| su.trans.sum(:sum)}.reverse.each do |su| %>
<li><%= su.name %> <%= su.trans.sum(:sum) %></li>
<% end %>
</ol>
There are 2 ways I see that you can do this. The simpler approach (albeit less efficient in the sorting), would be to aggregate the sums by buyer_id
and then inject that into the list of buyers:
<% buyer_sums = @seller.trans.group(:buyer_id).sum(:sum) %>
<% Buyer.where(id: buyer_sums.keys).sort_by {|b| -buyer_sums[b.id]}.each do |buyer| %>
<%= buyer.name %> <%= buyer_sums[buyer.id] %>
<% end %>
A second approach would be to fetch the sums together with the buyer:
<% Buyer.joins(:trans).merge(@seller.trans).
select("buyers.*, SUM(`sum`) sums").order('SUM(`sum`)').each do |buyer| %>
<%= buyer.name %> <%= buyer.sums %>
<% end %>
The advantage of the second approach is that you aren't sorting after retrieving the entire collection, which matters for scalability especially as related to pagination.
Note that the backtick (`) escape character used here is for MySQL, but should be replaced with the appropriate DB convention if otherwise (e.g. double quote for PostgreSQL). Alternatively, use the quote_column_name
method to do this smarter :)
这篇关于需要有关连接表的帮助,将结果限制为仅资源 ID的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!