Rails AjaxDatatablesRails::ActiveRecord LEFT JOIN to the same model lead to error for search

Question

My transaction model had two fields that referenced stockroom model objects. This causes to an error during search. Searching or sorting by any of these fields only uses the data from the first 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

Answer 1

I`ve tried many ways to fix this error. In the end I was able to do it. I'm leaving this post here in case someone else encounters the same error. Or if someone knows a better way to solve it. Customizing the AjaxDatatablesRails filter_records(records) method and use AS for my request in get_raw_records worked for me.

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"] used for searching in single column instead of all searchable columns