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

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.

CodePudding user response：

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)