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.
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