Dynamic direction of ORDER in ActiveRecord query when using a JOIN

Question

I'm attempting to cleanup some code that was flagged as being vulnerable to SQL injection. As such, I am converting a lot of raw SQL query strings over to ActiveRecord's methods. One type of query that I'm having an issue finding an equivalent for is when I want to apply ordering (order) to a nested attribute.

If I have an Account and User object and I wanted to order the result by the user_id, I could do this (this is what the code currently has):

->(direction) { Account.joins(:users).order("users.id #{direction}").first }

However, this is vulnerable to SQL injection.

I know that if I'm ordering by attributes of Account, you can just pass a hash to order

->(direction) { Account.joins(:users).order(created_at: direction).first }

However, using a string as the attribute to sort by (since it's nested) doesn't result in the correct query:

->(direction) { Account.joins(:users).order('users.id': direction).first }
# SELECT  `accounts`.* FROM `accounts` INNER JOIN `users` ON `users`.`account_id` = `accounts`.`id`  ORDER BY `accounts`.`users.id` DESC LIMIT 1

And using a nested hash also doesn't work

->(direction) { Account.joins(:users).order(users: {id: direction}).first }

I know that I can enforce direction to be one of the accepted values with some additional logic, but was wondering if there was an easy way to do this via ActiveRecord's querying that I was missing.

Answer 1

Ordering by a joined association is only supported via strings I fear.

However, it is possible to merge scopes which allows you to write:

->(direction) { Account.joins(:users).merge(User.order(id: direction})).first }

This does prevent values other than [:asc, :desc, :ASC, :DESC, "asc", "desc", "ASC", "DESC"] for direction (plucked from the ArgumentError message). In my opinion it comes at the cost of decreased readability but one can try to mitigate the problem and increase reusability at the same time by defining the order as a named scope in the User class.

class User
   ...
   self.ordered_by_id(direction)
     order(id: direction})
   end
   ...
end

Which enables you to use

->(direction) { 
  Account.joins(:users).merge(User.ordered_by_id(direction)).first
} 

Granted, in the current example this does not shine yet, but using merge with more complicated scopes can greatly reduce duplication.

Answer 2

I think order isn't as advanced as it could be.

But as you expect asc or desc only you could filter it:

direction = (%w[asc desc] & [direction.downcase])[0] || 'asc'
Account.joins(:users).order("users.id #{direction}").first