4 ms·

ActiveRecord is terrible for any kind of complex database work. If your data models are simple or you decide to shoehorn your entire data model into an "object"

ActiveRecord is terrible for any kind of complex database work. If your data models are simple or you decide to shoehorn your entire data model into an "object" representation then ActiveRecord will allow you to work, but if you need to run queries like "return every product that represents less than 2% of total revenue for the last year excluding products purchased by our top 10 most profitable clients" ActiveRecord is going to give you a very inefficient solution where you'll also end up writing Ruby code to accomplish what the database is already capable of.

igor47 10y ago
ActiveRecord is not the right tool for a job like this. It's meant to return records needed for serving normal web traffic, not for running data analytics. This is a better job for a tool which can run queries like this outside of a latency-sensitive environment (hadoop, presto, whatever). > you'll also end up writing Ruby code to accomplish what the database is already capable of. This is for the best. Usually, you have only a few database servers, while you have lots and lots of web servers. If you have a computationally intensive task, it's better to perform it on those web servers, which you can scale horizontally. In our environment, the problem is usually the opposite -- it's way too easy in ActiveRecord to ask the database to do computation like sorting for you. Also, it can be hard to predict what impact an additional clause will have on the server's use of indexes. We try to get engineers new to Rails to write the simplest, most performant queries and then use ruby to do any soft of complicated transformation or computation.
- gaius 10y ago
  This is a better job for a tool which can run queries like this outside of a latency-sensitive environment (hadoop, presto, whatever) Actually the right tool for this is plain old SQL. Even with nice formatting this is probably a 10-liner at most. If you have a computationally intensive task, it's better to perform it on those web servers, which you can scale horizontally It's only computationally intensive if you're doing it wrong.
  - module0000 10y ago
    SQL is the right tool for the job! I replied to tell you that fewer and fewer people think that way....which is terrifying. "Elegant" solutions are judged as such, by their ability to avoid SQL. It's not a hard language, why is everyone trying to avoid it at the expense of their applications speed and simplicity? When I post job adverts, a large portion of applicants balk at the SQL questions: "just use PDO, or ActiveRecord, or ADO, or SQLAlchemy". Which is fine, if they can explain the difference between inner, outer, left/right joins - and they can't. SQL will be the next COBOL at this rate, or at least I hope so for selfish and monetary reasons.
- mikekchar 10y ago
  Somewhat ironically, perhaps, it goes back to one of the author's complaints: ActiveRecord is not really what you need for complex data. The idea that there is a 1:1 relationship between a table and an object is a poor one. Normally you want a datamapper to map between the model object(s) and the saved abstraction. To make it a bit more clear, it's exactly the same separation of concerns that you want when you write a view for the UI. The controller takes one or more model objects and uses the view to generate an abstraction for the UI. When we save and fetch information from the DB (or send data out on the wire as JSON, for example) we want the exact same thing. BTW, be very careful about doing all your computation on the ruby/rails side. It's a good idea from a certain perspective, but you are building a very nice bottleneck there if you have anything of real complexity. For example if you have a report that needs to sort through millions of records, you are going to be hanging your server for a loooong time. As you say, in those kinds of situations, you really want to query a service that has the ability to scale the requests more reasonably. Rails is incredibly poor at this kind of thing. My own personal opinion is that there is very little that rails gives me that is worth the pain that Rails imposes. I'm much better off building something with Sinatra. What Rails does do very well (which the author also acknowledges) is reducing the number of things you need to know to get up and running quickly. But if you are going to pay the kind of salary I demand, then you will expect me to beyond that level ;-)
- fixermark 10y ago
  Agreed. And the challenge with using Rails is that when you hit a problem for which ActiveRecord is not the right tool, you have to now discard your entire process because the whole of the Rails framework is assuming state manipulation via the ActiveRecord abstraction. "Rails" is an appropriate term for the framework; step too far off of it, and you find you're on rocky terrain.
ikawe 10y ago
I keep telling people to accomplish this by encapsulating the query in a DB view, and using a read only active record model in front of it. You can then query the table through the AR interface, and use has_many and friends. Unlike using execute_sql where everything is a string, type coercion works as you'd expect. This solves the problem if you know ahead of time the format of the query, e.g. The example you gave would probably fit well. But if you're offering some kind of query building interface, well god help you. No one has told me I'm wrong yet with this approach and it's been working great for years.
- sanderjd 10y ago
  Yeah, views are a good approach. Adding more first-class support for them would be a valuable improvement.
vinceguidry 10y ago
Don't do complex database work with ActiveRecord. What I like to do is these days is do backend jobs outside of Rails. (ActiveJob doesn't support RabbitMQ yet, I may change my mind about this when it does) Then use whatever toolset you want without Rails getting in your way.
necrodome 10y ago
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.

igor47 10y ago

ActiveRecord is not the right tool for a job like this. It's meant to return records needed for serving normal web traffic, not for running data analytics. This is a better job for a tool which can run queries like this outside of a latency-sensitive environment (hadoop, presto, whatever). > you'll also end up writing Ruby code to accomplish what the database is already capable of. This is for the best. Usually, you have only a few database servers, while you have lots and lots of web servers. If you have a computationally intensive task, it's better to perform it on those web servers, which you can scale horizontally. In our environment, the problem is usually the opposite -- it's way too easy in ActiveRecord to ask the database to do computation like sorting for you. Also, it can be hard to predict what impact an additional clause will have on the server's use of indexes. We try to get engineers new to Rails to write the simplest, most performant queries and then use ruby to do any soft of complicated transformation or computation.

gaius 10y ago
This is a better job for a tool which can run queries like this outside of a latency-sensitive environment (hadoop, presto, whatever) Actually the right tool for this is plain old SQL. Even with nice formatting this is probably a 10-liner at most. If you have a computationally intensive task, it's better to perform it on those web servers, which you can scale horizontally It's only computationally intensive if you're doing it wrong.
- module0000 10y ago
  SQL is the right tool for the job! I replied to tell you that fewer and fewer people think that way....which is terrifying. "Elegant" solutions are judged as such, by their ability to avoid SQL. It's not a hard language, why is everyone trying to avoid it at the expense of their applications speed and simplicity? When I post job adverts, a large portion of applicants balk at the SQL questions: "just use PDO, or ActiveRecord, or ADO, or SQLAlchemy". Which is fine, if they can explain the difference between inner, outer, left/right joins - and they can't. SQL will be the next COBOL at this rate, or at least I hope so for selfish and monetary reasons.
mikekchar 10y ago
Somewhat ironically, perhaps, it goes back to one of the author's complaints: ActiveRecord is not really what you need for complex data. The idea that there is a 1:1 relationship between a table and an object is a poor one. Normally you want a datamapper to map between the model object(s) and the saved abstraction. To make it a bit more clear, it's exactly the same separation of concerns that you want when you write a view for the UI. The controller takes one or more model objects and uses the view to generate an abstraction for the UI. When we save and fetch information from the DB (or send data out on the wire as JSON, for example) we want the exact same thing. BTW, be very careful about doing all your computation on the ruby/rails side. It's a good idea from a certain perspective, but you are building a very nice bottleneck there if you have anything of real complexity. For example if you have a report that needs to sort through millions of records, you are going to be hanging your server for a loooong time. As you say, in those kinds of situations, you really want to query a service that has the ability to scale the requests more reasonably. Rails is incredibly poor at this kind of thing. My own personal opinion is that there is very little that rails gives me that is worth the pain that Rails imposes. I'm much better off building something with Sinatra. What Rails does do very well (which the author also acknowledges) is reducing the number of things you need to know to get up and running quickly. But if you are going to pay the kind of salary I demand, then you will expect me to beyond that level ;-)
fixermark 10y ago
Agreed. And the challenge with using Rails is that when you hit a problem for which ActiveRecord is not the right tool, you have to now discard your entire process because the whole of the Rails framework is assuming state manipulation via the ActiveRecord abstraction. "Rails" is an appropriate term for the framework; step too far off of it, and you find you're on rocky terrain.

ikawe 10y ago

I keep telling people to accomplish this by encapsulating the query in a DB view, and using a read only active record model in front of it. You can then query the table through the AR interface, and use has_many and friends. Unlike using execute_sql where everything is a string, type coercion works as you'd expect. This solves the problem if you know ahead of time the format of the query, e.g. The example you gave would probably fit well. But if you're offering some kind of query building interface, well god help you. No one has told me I'm wrong yet with this approach and it's been working great for years.

sanderjd 10y ago
Yeah, views are a good approach. Adding more first-class support for them would be a valuable improvement.

vinceguidry 10y ago

Don't do complex database work with ActiveRecord. What I like to do is these days is do backend jobs outside of Rails. (ActiveJob doesn't support RabbitMQ yet, I may change my mind about this when it does) Then use whatever toolset you want without Rails getting in your way.

necrodome 10y ago

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.