includes_same_table_twice.md

includes同一个表两次的解决方案(不完美)

相关文章

ActiveRecord关联的命名约定/规范

问题重现

members表有email字段和id字段

transfers表(邮件记录)只有from_member_id和to_member_id字段与members表关联

[!TIP|label:按邮箱查询] 如何「同时」按发件人邮箱和收件人邮箱查询邮件记录表

新版写法

before_action only: [:index, :download_csv] do
  @transfers = InternalTransfer.includes(:from_member, :to_member, :currency)
  if params[:from_email].present? && params[:to_email].present?
    @transfers = @transfers.where("members.email = ?", params[:from_email]).references(:from_member)
    if params[:to_email].present?
      filter_by_to_email = @transfers.select { |x| x.to_member.email == params[:to_email] }
      @transfers = @transfers.where(id: filter_by_ato_email.map(&:id))
    end
  else
    @transfers = @transfers.where("members.email = ?", params[:to_email]).references(:to_member) if params[:to_email].present?
  end
end

旧版写法(joins)

private
def search_by_params
  @transfers = InternalTransfer
  if params[:from_email].present?
    if params[:to_email].present?
      @transfers = @transfers
        .joins("INNER JOIN members AS sender ON from_member_id = sender.id")
        .joins("INNER JOIN members AS receiver ON to_member_id = receiver.id")
        .where('sender.email = ? AND receiver.email = ?', params[:from_email], params[:to_email])
    else
      @transfers = @transfers
        .joins(:from_member)
        .where('email = ?', params[:from_email])
    end
  elsif params[:to_email].present?
    @transfers = @transfers
      .joins(:to_member)
      .where('email = ?', params[:to_email])
  end
end