Suever Suever - 1 month ago 11
Ruby Question

Dynamic direction of ORDER in ActiveRecord query when using a JOIN

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 Source

Ordering by a joined association does 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.