ruby on rails - Compare rows with similar data from two models -

a dealercommission table has following attributes, among others: phone_id, commission_name, payment, status, discrepancy, created_at

a carriercommission table has following attributes, among others: phone_id, commissions_name, payment, created_at

i want select range of dates dealer commissions , compare records same phone_id see if payment amount match. if match, want update status "paid." if don't match, want update status "overpaid" or "underpaid." if there no matching phone_id in carrier_commission, update status "nonpaid." discrepancy value updated accordingly.

this set off user clicking "reconcile" link linked "reconcile" action in dealer commissions controller

i have following non-working code:

# dealer commissions index <%= link_to 'reconcile', reconcile_dealer_commissions_path %>   # dealer commissions controller def reconcile   dealercommission.reconciliation end    # dealer commission model class dealercommission < activerecord::base   belongs_to :dealer    def self.reconciliation     dealer_commissions = dealercommission.where("created_at > date.yesterday")     carrier_commissions = carriercommission.where("created_at > date.yesterday")     dealer_commissions.find_each |com|       if com.payment == carrier_commissions.payment         com.update(status: "paid")       elsif com.payment < carrier_commissions.payment         com.update(status: "underpaid")       elsif com.payment > carrier_commissions.payment         com.update(status: "overpaid")       end     end   end  end 

obviously, code doesn't reflect full requirements @ least wanted comparison working.

i looking way retrieve both carrier , dealer commissions in single database query, using eager_load, , specifying join condition sql fragment , using references.

http://guides.rubyonrails.org/active_record_querying.html#specifying-conditions-on-the-joined-tables

that ought matching dealer , carrier commission instances, , can perform comparison , update dealer commission appropriately.