'Parametrized join in Rails 4

I am doing manual join and I need to pass a parameter to its ON clause:

Foo.joins("LEFT OUTER JOIN bars ON foos.id = bars.foo_id AND bars.baz = #{baz}")

Is there a way to pass baz as a parameter, to avoid potential injection problems? There is a method sanitize_sql_array, but I'm not sure how to make use of it in this case.

Note: I can't use where because it's not the same.

ruby-on-railsruby-on-rails-4parametersleft-join


Solution 1:[1]

With sanitize_sql_array, that would be:

# Warning: sanitize_sql_array is a protected class method, be aware of that to properly use it in your code
ar = ["LEFT OUTER JOIN bars ON foos.id = bars.foo_id AND bars.baz = %s", baz]

# Within a class method in foo model:
sanitized_sql = sanitize_sql_array(ar)
Foo.joins(sanitized_sql)

Tried it and it worked.

Solution 2:[2]

Active record models have sanitize class method, so you could do:

Foo.joins("LEFT OUTER JOIN bars ON foos.id = bars.foo_id AND bars.baz = #{Foo.sanitize(baz)}")

__

It's been removed starting rails 5.1, use ActiveRecord::Base.connection.quote() instead

Solution 3:[3]

Arel can do it.

baz = "i am a baz"
foos = Arel::Table.new('foos') # or Foo.arel_table, if this is an AR model
bars = Arel::Table.new('bars')
join = foos.outer_join(bars).on(
  foos[:id].eq(bars[:foo_id]),
  bars[:baz].eq(baz))
puts join.to_sql

This produces

SELECT FROM "foos"
LEFT OUTER JOIN "bars" ON
  "foos"."id" = "bars"."foo_id"
  AND "bars"."baz" = 'i am a baz'

Now if you want to get that back into an ActiveRecord query and not just print the SQL:

Foo.joins(join.join_sources)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3 Phil Frost

Related Questions