Display Join Records Data in order of they created in Postgresql and Ruby On Rails

Question

I have three tables in postgresql database:

1. Members

   • id
   • first_name
   • last_name
   • created_at

2. Payments (Like Debit)

   • id
   • member_id
   • amount
   • created_at
   • type

3. Fees (Like Credit)

   • id
   • member_id
   • amount
   • created_at
   • type

I would like to show them in a single view say billing statements: and I used following queries:

select * from members as t1 left join payments as t2 on (t1.id=t2.member_id) left join fees as t3 on (t1.id=t3.member_id) 

and it shows me following results:

id | Amount | created_at | Type
1   100      25-12-2013   Fee
2   100      29-12-2013   Fee
3   100      25-12-2013   Payment
4   100      23-12-2013   Payment

But issue is that it shows first fees record and then payments while I need it should be order by created_at and it should be like below:

id | Amount | created_at | Type
1   100      23-12-2013   Payment
2   100      25-12-2013   Fee
3   100      25-12-2013   Payment
4   100      29-12-2013   Fee

Here is details of models:

class Fee < ActiveRecord::Base

  # Setup accessible (or protected) attributes for your model
  attr_accessible :member_id,:fee_type,:amount,:reason,:details,:date

  # Setup relations with relative models
  belongs_to :member

  # Validates rule here
  validates_numericality_of :member_id,:fee_type,:amount

  validates :reason,
    :format => {:with => /^[-a-zA-Z0-9_ ]+$/ },
    :length => {:maximum => 255},
    :presence =>true
  validates :details,
    :format => {:with => /^[-a-zA-Z0-9_ ]+$/ },
    :length => {:maximum => 255},
    :allow_blank =>true
  validates :amount,
    :presence => true
  validates :member_id,
    :presence => true
  validates :date,
    :presence => true

  # Search start here
  self.per_page = 10

end

class Payment < ActiveRecord::Base

  # Setup accessible (or protected) attributes for your model
  attr_accessible :member_id,:payment_category_id,:payment_type_id,:payment_date,:amount,:notes,:is_send_email,:last_sent,:date_deposite
  # Setup relations with relative models 
  belongs_to :payment_type
  belongs_to :payment_category
  belongs_to :member

  # Validates rule here
  validates_numericality_of :member_id,:payment_category_id,:payment_type_id,:amount

  validates :notes,
    :format => {:with => /^[-a-zA-Z0-9_ ]+$/ },
    :length => {:maximum => 255},
    :allow_blank =>true
  validates :amount,
    :presence => true,
    :length => {:maximum => 15}
  validates :member_id,
    :presence => true
  validates :payment_date,
    :presence => true

  # Pagiination start here
  self.per_page = 10

end

class Member < ActiveRecord::Base

  has_many :payment, dependent: :destroy, -> { order :created_at }
  has_many :fee, dependent: :destroy, -> { order :created_at }
  has_many :events, :through => :participants

end

Answer 1

Depending on how many records you are pulling out, you can also do the sorting in ruby after pulling them out of the database eg

transactions = member.payments + member.fees
transactions = transactions.flatten.sort_by(&:created_at)

Answer 2

If I were you, I'd use ActiveRecord Associations like this:

#app/models/member.rb
Class Member < ActiveRecord::Base
   has_many :payments, -> { order :created_at }
   has_many :fees, -> { order :created_at }
end

#app/models/payment.rb
Class Payment < ActiveRecord::Base
   belongs_to :member
end

#app/models/fee.rb
Class Fee < ActiveRecord::Base
   belongs_to :member
end

This should give you the ability to pull the associative data you need with it ordered, like this:

@member = Member.find 1
@member.payments #-> should be ordered by created_at