Ah, QueryAttribute was the part I was missing, didn't have time to dig through the code and follow how something like #find was specifying the bind variables (correctly). That makes sense.
Thanks, Al On Fri, Jun 26, 2015 at 2:10 PM, Roque Pinel <[email protected]> wrote: > 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 <[email protected]> > 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 <[email protected] >> > 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 [email protected]. >>> To post to this group, send email to [email protected]. >>> 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 [email protected]. >> To post to this group, send email to [email protected]. >> 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 [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/rubyonrails-core. For more options, visit https://groups.google.com/d/optout.
