3 ms·

You can drop to sql if you find yourself fighting against ActiveRecord. It is simple as ActiveRecord::Base.connection.execute(sql) where you will end up working

You can drop to sql if you find yourself fighting against ActiveRecord. It is simple as ActiveRecord::Base.connection.execute(sql) where you will end up working with arrays and hashes.

philwelch 10y ago
OK, what if you want to generate the SQL dynamically with attributes? connection.execute doesn't do sanitization for you by itself (though you can call a private method to do that). If the generation is more complicated than that, you also have to have code to concatenate together strings of SQL code. Not because Rails doesn't have a SQL generation library, just because it's tightly coupled to ActiveRecord and fuck you and the horse you rode in on if that isn't good enough for you.
- hsod 10y ago
  > what if you want to generate the SQL dynamically with attributes? connection.execute doesn't do sanitization for you by itself (though you can call a private method to do that). You just answered your own question. You sanitize the string and then you call connection.execute. Not sure I understand your other issue but it doesn't seem too compelling.
  - philwelch 10y ago
    Yeah but there's no public method to sanitize the string. Having to call a private method works, and I've done it, but it's hacky and fragile and the only way it could pass code review with a clean conscience IMO is if you add an apologetic comment saying, "I know I'm using .send to call a private method and that this could break at any time if we update our Rails version because private methods aren't supported, but there's no other way to sanitize a string programmatically in this context, blame DHH". And I don't like having to put those kinds of comments in my code. > Not sure I understand your other issue but it doesn't seem too compelling. If you don't understand the other issue, how do you feel qualified to comment on whether it seems compelling? Use case: I want to pull up a table of aggregated data for my user. The table is built by joining multiple tables together. The user can dynamically select which columns in the table he wants to see.
- bloopletech 10y ago
  I understand your general frustration, but to respond to your specific point here, AR does actually support executing generic parameterized SQL statements: Model.find_by_sql(query, binds=[]) http://devdocs.io/rails~4.2/activerecord/querying#method-i-find_by_sql http://devdocs.io/rails~4.2/activerecord/querying#method-i-f... Model.connection.select_all(query, name = nil, binds=[]) http://devdocs.io/rails~4.2/activerecord/connectionadapters/databasestatements#method-i-select_all http://devdocs.io/rails~4.2/activerecord/connectionadapters/...
  - philwelch 10y ago
    Yeah, I generally advocate for find_by_sql and select_all, but think about what that actually does. It instantiates a bunch of ActiveRecord objects that have to be of a particular model (what if the query joins tables together and returns a result set that doesn't actually neatly contain the intended columns of a given model? I can't even use ActiveRecord::Base.find_by_sql, I have to choose an actual ActiveRecord model arbitrarily), has the columns of the result set dynamically bound to it as methods during runtime (slow), also has the methods of the model itself bound to it, which may have unexpected behavior based on the query (especially if we just choose an arbitrary model), and in exchange we get to treat the result set as an array of structs rather than an array of hashes (which admittedly has its performance advantages, but not if the fields have to be dynamically bound to each object as methods!). Oh, and if you're writing an INSERT statement you have to use connection.execute after all. Have fun!

You can drop to sql if you find yourself fighting against ActiveRecord. It is simple as ActiveRecord::Base.connection.execute(sql) where you will end up working with arrays and hashes.

philwelch 10y ago

OK, what if you want to generate the SQL dynamically with attributes? connection.execute doesn't do sanitization for you by itself (though you can call a private method to do that). If the generation is more complicated than that, you also have to have code to concatenate together strings of SQL code. Not because Rails doesn't have a SQL generation library, just because it's tightly coupled to ActiveRecord and fuck you and the horse you rode in on if that isn't good enough for you.

hsod 10y ago
> what if you want to generate the SQL dynamically with attributes? connection.execute doesn't do sanitization for you by itself (though you can call a private method to do that). You just answered your own question. You sanitize the string and then you call connection.execute. Not sure I understand your other issue but it doesn't seem too compelling.
- philwelch 10y ago
  Yeah but there's no public method to sanitize the string. Having to call a private method works, and I've done it, but it's hacky and fragile and the only way it could pass code review with a clean conscience IMO is if you add an apologetic comment saying, "I know I'm using .send to call a private method and that this could break at any time if we update our Rails version because private methods aren't supported, but there's no other way to sanitize a string programmatically in this context, blame DHH". And I don't like having to put those kinds of comments in my code. > Not sure I understand your other issue but it doesn't seem too compelling. If you don't understand the other issue, how do you feel qualified to comment on whether it seems compelling? Use case: I want to pull up a table of aggregated data for my user. The table is built by joining multiple tables together. The user can dynamically select which columns in the table he wants to see.
bloopletech 10y ago
I understand your general frustration, but to respond to your specific point here, AR does actually support executing generic parameterized SQL statements: Model.find_by_sql(query, binds=[]) http://devdocs.io/rails~4.2/activerecord/querying#method-i-find_by_sql http://devdocs.io/rails~4.2/activerecord/querying#method-i-f... Model.connection.select_all(query, name = nil, binds=[]) http://devdocs.io/rails~4.2/activerecord/connectionadapters/databasestatements#method-i-select_all http://devdocs.io/rails~4.2/activerecord/connectionadapters/...
- philwelch 10y ago
  Yeah, I generally advocate for find_by_sql and select_all, but think about what that actually does. It instantiates a bunch of ActiveRecord objects that have to be of a particular model (what if the query joins tables together and returns a result set that doesn't actually neatly contain the intended columns of a given model? I can't even use ActiveRecord::Base.find_by_sql, I have to choose an actual ActiveRecord model arbitrarily), has the columns of the result set dynamically bound to it as methods during runtime (slow), also has the methods of the model itself bound to it, which may have unexpected behavior based on the query (especially if we just choose an arbitrary model), and in exchange we get to treat the result set as an array of structs rather than an array of hashes (which admittedly has its performance advantages, but not if the fields have to be dynamically bound to each object as methods!). Oh, and if you're writing an INSERT statement you have to use connection.execute after all. Have fun!