ruby Rails AjaxDatatablesRails::ActiveRecord LEFT JOIN到同一模型导致搜索错误

lnvxswe2  于 11个月前  发布在  Ruby
关注(0)|答案(1)|浏览(90)

我的事务模型有两个引用stockroom模型对象的字段。这会导致搜索过程中出错。按这些字段中的任何一个进行搜索或排序都只使用来自第一个left_join的数据。

class Transaction < ApplicationRecord
  belongs_to :stored_product
  belongs_to :user
  belongs_to :from_stockroom, class_name: 'Stockroom', foreign_key: 'from_stockroom_id', optional: true
  belongs_to :to_stockroom, class_name: 'Stockroom', foreign_key: 'to_stockroom_id', optional: true
  attr_accessor :user_selection

字符串

ymdaylpp

ymdaylpp1#

我已经尝试了很多方法来修复这个错误。最后我能够做到这一点。我离开这个帖子在这里的情况下,其他人遇到同样的错误。或者如果有人知道一个更好的方法来解决它。自定义AjaxDatatablesRails filter_records(records)方法,并使用AS为我的请求在get_raw_records为我工作。

class TransactionsDatatable < AjaxDatatablesRails::ActiveRecord
  extend Forwardable
  def_delegators :@view, :current_user

  def initialize(params, opts = {})
    @view = opts[:view_context]
    super
  end

  def view_columns
    @view_columns ||= {
      id: { source: 'Transaction.id', cond: :eq, searchable: true },
      stored_product_id: { source: 'StoredProduct.name', searchable: true },
      user_id: { source: 'User.name', cond: :like, searchable: true },
      from_stockroom_id: { source: 'from_stockroom_name', cond: :like, searchable: true, orderable: true },
      to_stockroom_id: { source: 'to_stockroom_name', cond: :like, searchable: true, orderable: true },
      quantity: { source: 'Transaction.quantity', searchable: false, orderable: false },
      comment: { source: 'Transaction.comment', cond: :like, searchable: true, orderable: false },
      created_at: { source: 'Transaction.created_at', searchable: false }
    }
  end

  def data
    records.map do |record|
      {
        id: record.id,
        stored_product_id: record.stored_product.name,
        user_id: record.user.name,
        from_stockroom_id: record.from_stockroom.present? ? record.from_stockroom.name : 'Поставка',
        to_stockroom_id: record.to_stockroom.present? ? record.to_stockroom.name : 'Списание',
        quantity: record.quantity,
        comment: record.comment,
        created_at: record.created_at.strftime('%d.%m.%Y'),
        DT_RowId: record.id
      }
    end
  end

  def get_raw_records
    Transaction.where(deleted: false)
               .joins(:stored_product, :user)
               .joins('LEFT JOIN stockrooms AS from_stockrooms ON from_stockrooms.id = transactions.from_stockroom_id')
               .joins('LEFT JOIN stockrooms AS to_stockrooms ON to_stockrooms.id = transactions.to_stockroom_id')
               .select('transactions.*, from_stockrooms.name as from_stockroom_name, to_stockrooms.name as to_stockroom_name')
  end

  def filter_records(records)
    to_search_value = params[:columns]['4']['search']['value']
    from_search_value = params[:columns]['3']['search']['value']
    multiple_search_value = params['search']['value']
    records = records.where('LOWER(from_stockrooms.name) LIKE ?', "%#{from_search_value.downcase}%") if from_search_value.present?
    records = records.where('LOWER(to_stockrooms.name) LIKE ?', "%#{to_search_value.downcase}%") if to_search_value.present?

    return records.where(build_conditions) unless multiple_search_value.present?
    
    records.where(build_conditions)
           .or(records.where(build_conditions_for_selected_columns).and(stockrooms_colums_search(records,multiple_search_value)))
  end

  def stockrooms_colums_search(records, multiple_search_value)
    records.where('LOWER(to_stockrooms.name) LIKE ?', "%#{multiple_search_value.downcase}%")
           .or(records.where('LOWER(from_stockrooms.name) LIKE ?', "%#{multiple_search_value.downcase}%"))
  end
end

字符串
params[:columns]["4"]["search"]["value"]用于搜索单个列而不是所有可搜索列

相关问题