Efficient sorting of rows by multiple columns in Rails 3

Question

I have a table name transactions with following columns

created_at        | debit | credit | balance | account_id
2012-2-2 02:22:22 | 3000  | 0      | 0       | 8
2012-2-2 07:22:22 | 500   | 1000   | 0       | 8
2012-2-2 09:22:22 | 0     | 8000   | 0       | 8

2012-3-3 19:17:20 | 1000  | 0      | 0       | 8
2012-3-3 04:02:22 | 0     | 8000   | 0       | 8

Before calculating balance I need to

sort transactions by date (i.e. daily)
then sort by debit (higher debit must come first)

I have a million transactions distinguished by account_id. What is efficient way of sorting in such a scenario ?

Any answer will be appreciated.

I may be misunderstanding something, but wouldn't sorting by time on each date be the same as sorting by time? If you sort by time first, then any secondary sort won't really matter, unless two transactions happened exactly at the same time. Maybe you mean sort by date, then by debit? — operand
– operand, Commented Dec 28, 2012 at 12:35

Community · Accepted Answer · 2017-05-23 12:16:15Z

2

This sounds a lot like this question

Transaction.all(:order => "created_at DESC, debit DESC")

This kind of querying is exactly what relational databases are good at and with proper indexing, it should be efficient for you.

For a particular account…

Transaction.find_by_account_id(some_id, :order => "created_at DESC, debit DESC")

If this is setup as a proper ActiveRecord association on the account, you can set the order there so the association is always returned in the preferred order:

class Account
    has_many :transactions, :order => "created_at DESC, debit DESC"
end

This way, any time you ask an account for its transactions like Account.find(12345).transactions they'll be returned in the preferred sorting automatically.

edited May 23, 2017 at 12:16

CommunityBot

11 silver badge

answered Dec 28, 2012 at 14:21

jxpx777

3,6414 gold badges30 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

developer033 Over a year ago

I know this answer was posted in 2012, but to contribute to people who may need, I'll post here a way to do it in Rails 4.0+, since :order in has_many block has been deprecated and needs to be replaced with lambda scope block. You can do it using hash style: has_many :transactions, -> { order(created_at: :desc, debit: desc) }, or still as string: has_many :transactions, -> { order('created_at DESC, debit DESC') }

Collectives™ on Stack Overflow

Efficient sorting of rows by multiple columns in Rails 3

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related