Creating an escaped JOIN?

rubyonrails-talk
1

Hi,

When creating a custom find for a model, with AR it is possible to do something like:

:conditions => ['user_id = ?', params[:id]]

However, I would like to know if it is possible to do the same when specifying a JOIN:

:joins => ['LEFT OUTER JOIN orders ON (items.order_id = orders.id AND orders.user_id = ?', params[:id]]

I tried the above expression, but unfortunately, Rails didn't replace the question mark with its escaped value.

Any idea if this is possible? I need to move some filtering from the WHERE clause into the ON clause and have it all escaped.

2

Placeholders are not supported there indeed.

You cannot work with regular AR association API calls right? For example:

   # untested    user = User.find(params[:id])    items = user.orders.map(&:items).flatten.uniq

or say:

   # untested    items = Item.all(      :conditions => {'users.id' => params[:id]},      :joins => {:order => :user}    )

If not, you can still extract an integer from params[:id] and safely interpolate. That's easy with Integer() or #to_i.

-- fxn

3

> :joins => ['LEFT OUTER JOIN orders ON (items.order_id = orders.id AND > orders.user_id = ?', params[:id]]

Placeholders are not supported there indeed.

You cannot work with regular AR association API calls right? For example:

# untested user = User.find(params[:id]) items = user.orders.map(&:items).flatten.uniq

or say:

# untested items = Item.all( :conditions => {'users.id' => params[:id]}, :joins => {:order => :user} )

If not, you can still extract an integer from params[:id] and safely interpolate. That's easy with Integer() or #to_i.

and more generally the connection object's quote method (and variants) are useful. See also the sanitize_sql method.

Fred

4

Hi Fred,

sanitize_sql is the way to go, then I simply evaluate what it spits me out inside my :joins value.

Regards,