select from or order by a column from the joins table in Rails using ActiveRecord syntax

Question

Here's what I want to do:

Aquarium
  .select(:id, :name, :yr_built, 'fishes.name')
  .joins(:fishes)
  .where(fishes: {name: "Nemo"})
  .order('fishes.bday DESC')

This currently works as intended, because I put in raw SQL code. However, because I was told using raw SQL was a bad practice and leaves you open to vulnerabilities, I would like to refactor the select and order lines using the proper ActiveRecord syntax.

I know that inside a .where, you can find values from a joined table using the table_name: {column_name: "value"} syntax. And I know that if I wanted to order by a column in the aquariums table, I can do it like so: .order(col_name: :desc), because aquariums is the table that started with. But this doesn't seem to work when I try adding it to .order like this: .order(fishes: {bday: :desc}) I am also unsure how to convert 'fishes.name' in .select from raw SQL, because it is also coming from the joins table.

Answer 1

There is no any risk in your solution. Even while you are using some string parameters for ActiveRecord - there is nowhere to put SQL injection there. Just change 'fishes.name' to 'fishes.name AS fish_name' to preserve both names in results.

However if you prefer SQL-string-free solutions (like I do) you can use Arel to white something like this.

Aquarium
  .joins(:fishes)
  .select(:id, :name, :yr_built, Fish.arel_table[:name].as('fish_name'))
  .where(fishes: { name: 'Nemo'})
  .order(Fish.arel_table[:updated_at].desc)