CODEWITHSUNDEEP
ruby-on-railsactiverecord
Darkstarone
Darkstarone

Reputation: 4730

Rails where or select for query chaining

So when querying a database, I know it's much better to use where on the initial query like so:

pending = Request.where("status = ?", "Pending").order! 'created_at DESC'

However, if I need to further filter this information, I can do it with either where or select:

high_p = pending.where("priority = ?", "High Priority")
normal_p = pending.where("priority = ?", "Priority")

Or

high_p = pending.select{|x| x.priority == "High Priority"}
normal_p = pending.select{|x| x.priority == "Priority"}

My question is which of these is better from a performance standpoint? Should we always use where? Or does select have a use case when the whole database isn't being searched?

Upvotes: 5

Views: 3989

Answers (1)

SteveTurczyn
SteveTurczyn

Reputation: 36860

where is much better from a performance standpoint. where modifies the SQL so that the DB does the "heavy lifting" of identifying records to retrieve.

select retrieves all records that meet the initial where condition, converts them into an array, and uses `Array#select' so the selection is happening at the rails end and you've retrieved more records from the db than you needed and done more processing than is necessary.

Select does have an advantage in that you can select based on model methods that may not be simply extracted from table columns. For example you may have a model method #bad_credit? which is determined by, say, a credit limit, age if outstanding invoices, and type of account.

Upvotes: 14

Related Questions