Welcome to Working With Rails

 

Discussion Forums

Discuss all things Ruby on Rails with perhaps the web's most vibrant group of Ruby on Rails enthusiasts.
Help with complex ActiveRecord search
3 Posts
Help with complex ActiveRecord search

I have to do a report of what we call account_transactions:

  class AccountTransaction < ActiveRecord::Base
     belongs_to :account
  end

This transactions come in two types, deposits and payments. Payments are for us to pay expenses so they belong to an expense, and the expense is to be paid to a Person or Company, for me it is a Contact:

  class Payment < AccountTransaction
    belongs_to :expense
  end

  class Expense < ActiveRecord::Base
    belongs_to :contact
    has_many :payments
  end

Same for deposits, but they belong to incomes:

  class Deposit < AccountTransaction
    belongs_to :income
  end

  class Income < ActiveRecord::Base
    belongs_to :contact
    has_many :deposits
  end

Deposit and Payment inherit from AccountTransaction using a single table inheritance wich means AccountTransaction have a "type" field. Now that I have stated all of this, here comes the question.

How can I perform a search of AccountTransactions (need the parent class) using contacts as a filter option. This filter must work along with other filters like date, range of dates and accounts. The result of this search should be the collection of deposits and payments associated to that contact. For example: get all the account transactions associated to "WorkingWithRails" contact, saved last week using my account with the number XXXXX.

Im thinking the best way to get this done is to create named_scopes and chain them all together depending on the filter options sent as parameters but the problem is the contact filter

AccountTransaction.of_contact(contact_id).of_account(account_id).on_date(date)

. All i can think of is to add a contact_id to the account_transaction and have another relationship there but im sure my boss wont like it.Any one has a good idea?

I just found my solution wich was pretty easy in fact, maybe many of the 31 visitors this post had until now noticed that and saved their time answering such a stupid question, this is the solution:

AccountTransaction < ActiveRecord::Base
  belongs_to :expense
  belongs_to :income
  named_scope :by_contact, lambda { |id| { :include => [{:expense, :contact}, {:income, :contact}],
    :conditions => "expenses.contact_id = #{id} OR incomes.contact_id = #{id}", :order => :date}}
end

I don't think your question was stupid. Executing the join with an OR clause as you described will get the answer you want, but I think the complexity in this problem stems from some inconsistencies in your domain model. From the top down, Contacts have many Expenses and Incomes, two classes which have no inheritance relationship. But then, both Expenses and Incomes have many AccountTransactions -- different types of Account Transactions, but they both join to the same table. So, a diagram of your table relationships describes a diamond.

Conceptually your Expenses and Payments each have to do with an agreement to transfer money, and thus each is associated with some number of actual money transfers. If you consider both an Expense or a Payment to be a type of Deal you could nominally rename your models this way:

Payment => OutTransaction Deposit => InTransaction Expense => OutDeal Income => InDeal

These aren't particularly compelling names, but they do make clear the very parallel relationships in your models. So, if AccountTransactions are STI, Deals (or Statements or Agreements, or whatever makes sense for your domain) really should be STI as well. If they're truly so wildly different in interface that STI would make a bizarre, Frankenstein-like table, you could use polymorphic associations to accomplish the same result.

After all this your find (or named_scope) would be much, much simpler:

AccountTransition.find(:all, :joins => { :deal => :contact }, :conditions => ["deals.contact_id = ?", contact_id])

Or, you could put a named_scope on the Deal model to simplify this further:

@contact.deals.account_transitions

You may find that simplifying your domain model in this way now makes your life a whole lot better as your domain model grows in the future.

One final point, you likely want :joins in your finder syntax rather than :include. The :include option tells the finder to instantiate and load the ActiveRecord objects for the specified associations, rather than simply joining through those tables to find what you're looking for.

3 Posts
Login to add your message