Need help getting started and migrate complicated getter logic from ActiveRecord


#1

I need some help getting started. I am working on a rails app but have read about ROM and want to try it. But I have some problems getting started. What do I need to create to get started?

We had some really slow code to render a reconciliation the server. Then we changed it to serve JSON and render it on the browser. Now it’s a lot faster but not that readable.

I would like to make the code more maintainable. I think ROM can help with that.
If I understood http://rom-rb.org/4.0/learn/getting-started/rails-setup/ correctly we can start to use ROM with side by side of ActiveRecord?

If so we want to use ROM to QUERY these tables.

I have created a ReconciliationGetterService which fetches all the resources I need. Enter them in some structure and returning i.
  Struct.new("Reconciliation", :id , :lines, :created_by_name, :created_at)
  def reconciliationInitialize id:, lines: [], created_by_name:, created_at:
    Struct::Reconciliation.new id, lines, created_by_name, created_at
  end

  Struct.new("ReconciliationLine", :id, :parent, :unit_id, :amount, :currency, :domestic_currency, :reconciliation_line_reasons, :base_foreign_amount, :foreign_amount, :exchange_rate)
  def reconciliationLineInitialize id:, parent:, unit_id:, amount:, currency:, domestic_currency:, reconciliation_line_reasons: [], base_foreign_amount:, foreign_amount:, exchange_rate:
    Struct::ReconciliationLine.new id, parent, unit_id, amount, currency, domestic_currency, reconciliation_line_reasons, base_foreign_amount, foreign_amount, exchange_rate
  end

  Struct.new("ReconciliationLineReason", :id, :amount, :closing_reason_name)
  def reconciliationLineReasonInitialize id:, amount:, closing_reason_name:
    Struct::ReconciliationLineReason.new id, amount, closing_reason_name
  end


  Struct.new("BankTransaction",
    :id, :type, :posting_date, :invoiceno, :kid, :amount, :account_number, :currency, :description,
    :unit_name, :bank_account_country_id, :polymorphic_comment_count)
  def bankTransactionInitialize id:, posting_date:, invoiceno:, kid:, amount:, account_number:, currency:, description:, unit_name:, bank_account_country_id:, polymorphic_comment_count:
    Struct::BankTransaction.new id, "BankTransaction", posting_date, invoiceno, kid, amount, account_number, currency, description, unit_name, bank_account_country_id, polymorphic_comment_count
  end

  Struct.new("Invoice", :id, :type, :invoice_type, :issue_date, :payment_due_date, :invoiceno, :kid, :customer_unit, :supplier_unit, :supplier_bank_account_raw, :customer_bank_account_raw, :supplier_status, :customer_status, :payable_amount_currency_code, :polymorphic_comment_count)
  def invoiceInitialize id:, invoice_type:, issue_date:, payment_due_date:, invoiceno:, kid:, customer_unit:, supplier_unit:, supplier_bank_account_raw:, customer_bank_account_raw:, supplier_status:, customer_status:, payable_amount_currency_code:, polymorphic_comment_count:
    Struct::Invoice.new id, "Invoice", invoice_type, issue_date, payment_due_date, invoiceno, kid, customer_unit, supplier_unit, supplier_bank_account_raw, customer_bank_account_raw, supplier_status, customer_status, payable_amount_currency_code, polymorphic_comment_count
  end


  Struct.new("Currency", :id, :code)
  def currencyInitialize id:, code:
    Struct::Currency.new id, code
  end

  Struct.new("Unit", :id, :country_id, :name)
  def unitInitialize id:, country_id:, name:
    Struct::Unit.new id, country_id, name
  end

  class ReconciliationGetterService
    def with_data_for_recent_matches reconciliations
      reconciliations = reconciliations
        .select("reconciliations.id")
        .select("reconciliations.created_at")
        .select("users.name as created_by_name")
        .joins(:created_by)
        .reduce({}) { |mem, e|
          mem[e.id] = reconciliationInitialize(
            id: e.id,
            created_by_name: e.created_by_name,
            created_at: e.created_at,
          )
          mem
        }

      lines = ReconciliationLine.where(reconciliation_id: reconciliations.keys)
        .select("reconciliation_lines.*")
        .select("currency.id AS currency_id")
        .select("currency.code AS currency_code")
        .select("domestic_currency.id AS domestic_currency_id")
        .select("domestic_currency.code AS domestic_currency_code")
        .joins("LEFT JOIN currencies AS currency ON currency.id = reconciliation_lines.currency_id")
        .joins("LEFT JOIN currencies AS domestic_currency ON domestic_currency.id = reconciliation_lines.domestic_currency_id")
        .order(:parent_type)
        .load

      parents = {"BankTransaction" => {}, "Invoice" => {}}
      get_bank_transactions(lines).each {|rec| parents["BankTransaction"][rec.id] = rec}
      get_invoices(lines).each {|rec| parents["Invoice"][rec.id] = rec}

      lines = lines.reduce({}) do |mem, line|
        line_struct = reconciliationLineInitialize(
          id: line.id,
          parent: parents[line.parent_type][line.parent_id],
          unit_id: line.unit_id,
          amount: line.amount,
          currency: currencyInitialize(id: line.currency_id, code: line.currency_code),
          domestic_currency: currencyInitialize(id: line.domestic_currency_id, code: line.domestic_currency_code),
          base_foreign_amount: line.base_foreign_amount,
          foreign_amount: line.foreign_amount,
          exchange_rate: line.exchange_rate
        )
        reconciliations[line.reconciliation_id].lines.push line_struct
        mem[line_struct.id] = line_struct;
        mem
      end

      reasons = ReconciliationLineReason
        .select("reconciliation_line_reasons.id")
        .select("reconciliation_line_reasons.reconciliation_line_id")
        .select("reconciliation_line_reasons.amount")
        .select("closing_reasons.name_json")
        .select(ClosingReason.select_closing_reasons_with_comment_string)
        .where(reconciliation_id: reconciliations.keys)
        .joins(:closing_reason)
        .joins(ClosingReason.join_unit_closing_reasons_string(lines.values.first.unit_id))
        .reduce({}) do |mem, reason|
          reason_struct = reconciliationLineReasonInitialize(
            id: reason.id,
            amount: reason.amount,
            closing_reason_name: reason.name,
          )
          lines[reason.reconciliation_line_id].reconciliation_line_reasons.push reason_struct
          mem[reason_struct.id] = reason_struct
          mem
        end

      reconciliations
    end

    def get_bank_transactions lines
      BankTransaction
        .select("bank_transactions.id")
        .select("posting_date")
        .select("invoiceno")
        .select("kid")
        .select("amount")
        .select("account_number")
        .select("currency")
        .select("description")
        .select("units.name AS unit_name")
        .select("bank_accounts.country_id AS bank_account_country_id")
        .select("COALESCE (comments.comment_count, 0) AS polymorphic_comment_count")
        .joins(:unit)
        .joins(:bank_account)
        .joins(PolymorphicComment.join_comment_string on: "comments.parent_id = bank_transactions.id AND comments.parent_type = 'BankTransaction'")
        .where(id: lines.select{|b| b.parent_type == "BankTransaction"}.map(&:parent_id))
        .map { |bt| bankTransactionInitialize(
          id: bt.id,
          posting_date: bt.posting_date,
          invoiceno: bt.invoiceno,
          kid: bt.kid,
          amount: bt.amount,
          account_number: bt.account_number,
          currency: bt.currency,
          description: bt.description,
          unit_name: bt.unit_name,
          bank_account_country_id: bt.bank_account_country_id,
          polymorphic_comment_count: bt.polymorphic_comment_count,
        )}
    end

    def get_invoices lines
      Invoice
        .select("invoices.id")
        .select(:invoice_type)
        .select(:issue_date)
        .select(:payment_due_date)
        .select(:invoiceno)
        .select(:kid)
        .select(:supplier_unit_id)
        .select(:customer_unit_id)
        .select(:supplier_name)
        .select(:supplier_bank_account_raw)
        .select(:customer_name)
        .select(:customer_bank_account_raw)
        .select(:supplier_status)
        .select(:customer_status)
        .select("currencies.code AS currency_code")
        .select("supplier_units.country_id AS supplier_unit_country_id")
        .select("customer_units.country_id AS customer_unit_country_id")
        .select("COALESCE (comments.comment_count, 0) AS polymorphic_comment_count")
        .joins(:payable_amount_currency)
        .joins("LEFT JOIN units AS supplier_units ON supplier_units.id = invoices.supplier_unit_id")
        .joins("LEFT JOIN units AS customer_units ON customer_units.id = invoices.customer_unit_id")
        .joins(PolymorphicComment.join_comment_string on: "comments.parent_id = invoices.id AND comments.parent_type = 'Invoice'")
        .where(id: lines.select{|b| b.parent_type == "Invoice"}.map(&:parent_id))
        .map { |inv| invoiceInitialize(
          id: inv.id,
          invoice_type: inv.invoice_type,
          issue_date: inv.issue_date,
          payment_due_date: inv.payment_due_date,
          invoiceno: inv.invoiceno,
          kid: inv.kid,
          supplier_unit: unitInitialize(id: inv.supplier_unit_id, country_id: inv.supplier_unit_country_id, name: inv.supplier_name),
          customer_unit: unitInitialize(id: inv.customer_unit_id, country_id: inv.customer_unit_country_id, name: inv.customer_name),

          supplier_bank_account_raw: inv.supplier_bank_account_raw,
          customer_bank_account_raw: inv.customer_bank_account_raw,
          supplier_status: inv.supplier_status,
          customer_status: inv.customer_status,
          payable_amount_currency_code: inv.currency_code,
          polymorphic_comment_count: inv.polymorphic_comment_count
        )}
    end

    def initialize reconciliations
      if !reconciliations.is_a? ActiveRecord::Relation
        raise ArgumentError.new("reconciliations must be ActiveRecord::Relation")
      end
      @reconciliations = reconciliations
    end

    def call
      return [] if @reconciliations.blank?
      with_data_for_recent_matches(@reconciliations).values
    end
  end
It produces some queries like these
SELECT reconciliation_lines.*,
       currency.id AS currency_id,
       currency.code AS currency_code,
       domestic_currency.id AS domestic_currency_id,
       domestic_currency.code AS domestic_currency_code
FROM reconciliation_lines
LEFT JOIN currencies AS currency ON currency.id = reconciliation_lines.currency_id
LEFT JOIN currencies AS domestic_currency ON domestic_currency.id = reconciliation_lines.domestic_currency_id
WHERE reconciliation_lines.reconciliation_id = 322758
ORDER BY reconciliation_lines.parent_type ASC,
SELECT bank_transactions.id,
       bank_transactions.posting_date,
       bank_transactions.invoiceno,
       bank_transactions.kid,
       bank_transactions.amount,
       bank_transactions.account_number,
       bank_transactions.currency,
       bank_transactions.description,
       units.name AS unit_name,
       bank_accounts.country_id AS bank_account_country_id,
       COALESCE (comments.comment_count, 0) AS polymorphic_comment_count
FROM bank_transactions
INNER JOIN units ON units.id = bank_transactions.unit_id
INNER JOIN bank_accounts ON bank_accounts.id = bank_transactions.bank_account_id
LEFT JOIN
    (SELECT COUNT(*) AS comment_count,
            parent_id,
            parent_type
     FROM polymorphic_comments
     GROUP BY parent_id,
              parent_type) AS comments ON comments.parent_id = bank_transactions.id
AND comments.parent_type = 'BankTransaction'
WHERE bank_transactions.id = 311718 


SELECT invoices.id,
       invoices.invoice_type,
       invoices.issue_date,
       invoices.payment_due_date,
       invoices.invoiceno,
       invoices.kid,
       invoices.supplier_unit_id,
       invoices.customer_unit_id,
       invoices.supplier_name,
       invoices.supplier_bank_account_raw,
       invoices.customer_name,
       invoices.customer_bank_account_raw,
       invoices.supplier_status,
       invoices.customer_status,
       currencies.code AS currency_code,
       supplier_units.country_id AS supplier_unit_country_id,
       customer_units.country_id AS customer_unit_country_id,
       COALESCE (comments.comment_count,
                 0) AS polymorphic_comment_count
FROM invoices
INNER JOIN currencies ON currencies.id = invoices.payable_amount_currency_id
LEFT JOIN units AS supplier_units ON supplier_units.id = invoices.supplier_unit_id
LEFT JOIN units AS customer_units ON customer_units.id = invoices.customer_unit_id
LEFT JOIN
    (SELECT COUNT(*) AS comment_count,
            parent_id,
            parent_type
     FROM polymorphic_comments
     GROUP BY parent_id,
              parent_type) AS comments ON comments.parent_id = invoices.id
AND comments.parent_type = 'Invoice'
WHERE invoices.deleted_at IS NULL
    AND invoices.id IN (243259, 243747);


SELECT reconciliation_line_reasons.id,
       reconciliation_line_reasons.reconciliation_line_id,
       reconciliation_line_reasons.amount,
       closing_reasons.name_json,
       CONCAT( CASE
                   WHEN unit_closing_reasons.comment IS NOT NULL
                        AND unit_closing_reasons.comment <> '' THEN unit_closing_reasons.comment || ' - '
                   ELSE ''
               END , CASE
                         WHEN closing_reasons.name_json->'nb' <> 'null' THEN closing_reasons.name_json->>'nb'
                         ELSE closing_reasons.name_json->>'nb'
                     END ) AS name
FROM reconciliation_line_reasons
INNER JOIN closing_reasons ON closing_reasons.id = reconciliation_line_reasons.closing_reason_id
LEFT JOIN unit_closing_reasons AS unit_closing_reasons ON unit_closing_reasons.closing_reason_id = closing_reasons.id
AND unit_closing_reasons.unit_id = 16964
WHERE reconciliation_line_reasons.reconciliation_id = 322758

I need help understanding what I need to create.

doc

I think I need to create a Relations for each of the tables.

then create a relation method to select the specific fields I’m interested in

Is that correct?
I wanted to create a minimal app to share, but I took to much time.

Thank you very much for any feedback


#2

You do need to create relations for each table.

You create methods in relations that return a query, you create repositories the methods there will need to execute the queries, in this case I believe your ReconciliationGetterSetter should actually be a repository.

For a life example of rom usage please take a look at https://github.com/icelab/berg