需要有关连接表的帮助,将结果限制为仅资源 ID [英] Need help on join table, limiting results to only the resource ID

查看:45
本文介绍了需要有关连接表的帮助,将结果限制为仅资源 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆