Parameterize an ActiveRecord #joins method

Question

I am refactoring a fairly complex query that involves chaining multiple .joins methods together. In one of these joins I am using a raw SQL query which uses string interpolation i.e joining WHERE foo.id = #{id}. I am aware that I can parameterize ActiveRecord #where by using the ? variable and passing in the arguments as parameters, but the joins method does not support multiple arguments in this fashion. For example:

Using:

Post.my_scope_name.joins("LEFT JOIN posts ON posts.id = images.post_id and posts.id = ?", "1") in order to pass in an id of 1 produces an ActiveRecord::StatementInvalid

because the generated SQL looks like this:

"LEFT JOIN posts ON posts.id = images.post_id and posts.id = ? 1"

What is the standard approach to parameterizing queries when using the joins method?

Answer 1

arel "A Relational Algebra" is the underlying query assembler for Rails and can be used to construct queries, conditions, joins, CTEs, etc. that are not high level supported in Rails. Since this library is an integral part of Rails most Rails query methods will support direct injection of Arel objects without issue (to be honest most methods convert your arguments into one of these objects anyway).

In your case you can construct the join you want as follows:

  posts_table = Post.arel_table
  images_table = Image.arel_table 
  some_id = 1

  post_join = Arel::Nodes::OuterJoin.new(
    posts_table,
    Arel::Nodes::On.new(
      posts_table[:id].eq(images_table[:post_id])
        .and(posts_table[:id].eq(some_id))
    )
  )

SQL produced:

  post_join.to_sql
  #=> "LEFT OUTER JOIN [posts] ON [posts].[id] = [images].[post_id] AND [posts].[id] = 1"

Then you just add this join to your current query

  Image.joins(post_join)
  #=> SELECT images.* FROM images LEFT OUTER JOIN [posts] ON [posts].[id] = [images].[post_id] AND [posts].[id] = 1