I believe the binds are used to improve the statement caching and not
suggested to be used directly.
Here is an example of an internal use when `Post.find(1)` executed:
id_query_attribute = ActiveRecord::Relation::QueryAttribute.new(:id, 1,
Post.type_for_attribute(:id))
Post.find_by_sql('select * from posts where id = ?', [id_query_attribute])
On Fri, Jun 26, 2015 at 1:05 PM, Al Tenhundfeld <tenhundf...@gmail.com>
wrote:
> If I'm following the code, it looks like the binds argument is passed
> through #select_all and eventually down to the adapter's #exec_query, e.g.,
> the MySQL adapter
> <https://github.com/rails/rails/blob/83821e2c4a32b17b153fcbcb247dec6e32f47284/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb#L253>.
> So, the behavior could be different depending on the adapter and even DB
> version. That makes sense, because different databases handle prepared
> statements and bind variables differently.
>
> And it looks like the documentation example of #find_by_sql, passing the
> array with "bind variables" into the sql arg, works because that sql arg is
> being passed to #sanitize_sql
> <https://github.com/rails/rails/blob/1d43458c148f9532a81b92ee3a247da4f1c0b7ad/activerecord/lib/active_record/sanitization.rb#L91>,
> which has its own implementation of replacing variables outside of/before
> the adapter. So, using this syntax isn't really using bind variables,
> passed to the database. It's more string interpolation and sanitation.
>
> So, following the code, I was able to get a query executing with the
> Postgres adapter and actually passing the bind variables to Postgres.
>
> *Example 1, using binds arg:*
> Project.find_by_sql('select * from projects where client_id = $1 and
> region = $2', [[nil, 123], [nil, 'West']])
> (NOTE: You can use the column name in place of nil, but I get a warning
> when I do that.)
> Results in this statements in the PG log:
> LOG: execute a3: select * from projects where client_id = $1 and region =
> $2
> DETAIL: parameters: $1 = '123', $2 = 'West'
>
> *Example 2, specifying the variables in the sql arg:*
> Project.find_by_sql(['select * from projects where client_id = ? and
> region = ?',123, 'West'])
> Results in:
> LOG: execute <unnamed>: select * from projects where client_id = 123 and
> region = 'West'
>
> So, you can see those are clearly different. In example 1, the statement
> is being prepared, and the bind variables are passed to Postgres. In
> example 2, the variables are being replaced by Rails via #sanitize_sql
> before the statement is passed on to the adapter, which results in an
> unnamed statement passed to Postgres with no bind variables. (I think,
> that's how I'm interpreting it at least.)
>
> I couldn't get a true bind variable example working with MySQL, but I
> don't have a convenient environment using recent versions of the adapter
> and MySQL server. I think this is related to Pat Shaughnessy's post on
> prepared
> statements
> <http://patshaughnessy.net/2011/10/22/show-some-love-for-prepared-statements-in-rails-3-1>
> .
>
> Following that logic, it's not too surprising that standard model methods
> result in different behavior in PG and MySQL:
>
> Project.find(1) using the Postgres adapter results in:
> LOG: execute a4: SELECT "projects".* FROM "projects" WHERE
> "projects"."id" = $1 LIMIT 1
> DETAIL: parameters: $1 = '1'
>
> Project.find(1) using (my older version of) the MySQL adapter results in
> the MySQL equivalent of:
> LOG: execute <unnamed>: SELECT "projects".* FROM "projects" WHERE
> "projects"."id" = 1 LIMIT 1
>
> I haven't really kept up with database internals enough to know how much
> of an improvement prepared statements yield. Years ago, prepared statements
> could give you a significant performance improvement, because the query
> parser can easily see two queries are the same, reuse copies of the
> execution plan, etc. My understanding is databases have gotten better about
> deriving similarities in queries and reusing plans, cached results, etc.
>
> I don't have an opinion (yet) on what should be done to improve the
> situation, but I hope that sheds a little light on what's happening with
> the binds arg and why you might want to use it.
>
> Cheers,
> Al
>
>
>
>
> On Fri, Jun 26, 2015 at 3:01 AM, Matias Korhonen <korhonen.m...@gmail.com>
> wrote:
>
>> There's also no test case (as far as I can find
>> <https://github.com/rails/rails/blob/3e36db4406beea32772b1db1e9a16cc1e8aea14c/activerecord/test/cases/finder_test.rb#L248-L265>)
>> that would cover passing the binds args to #find_by_sql
>>
>> Anyone know what it's supposed to be used for?
>>
>>
>>
>> On Thursday, 25 June 2015 18:48:10 UTC+3, T.J. Schuck wrote:
>>>
>>> The `bind` argument was added as `bind_values` all the way back in 2010,
>>> with no change to the documentation:
>>> https://github.com/rails/rails/commit/cc468d3ec81d6f1298fca91c0549584b36dafcc6
>>>
>>> So the docs are probably just wrong — I’m sure a documentation PR would
>>> be quickly merged.
>>>
>>> --
>> You received this message because you are subscribed to the Google Groups
>> "Ruby on Rails: Core" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to rubyonrails-core+unsubscr...@googlegroups.com.
>> To post to this group, send email to rubyonrails-core@googlegroups.com.
>> Visit this group at http://groups.google.com/group/rubyonrails-core.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "Ruby on Rails: Core" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to rubyonrails-core+unsubscr...@googlegroups.com.
> To post to this group, send email to rubyonrails-core@googlegroups.com.
> Visit this group at http://groups.google.com/group/rubyonrails-core.
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Core" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to rubyonrails-core+unsubscr...@googlegroups.com.
To post to this group, send email to rubyonrails-core@googlegroups.com.
Visit this group at http://groups.google.com/group/rubyonrails-core.
For more options, visit https://groups.google.com/d/optout.
