What is your convention for specifying complex SQL queries in Rails?

Question

I am fairly new to Rails and I was curious as to some of the conventions experts are using when they need to construct a very complex SQL query that contains many conditions. Specifically, keeping the code readable and maintainable.

There are a couple of ways I can think of:

Single line, in the call to find():

@pitchers = Pitcher.find(:all, "<conditions>")

Use a predefined string and pass it in:

@pitchers = Pitcher.find(:all, @conditions)

Use a private member function to return a query

@pitchers = Pitcher.find(:all, conditionfunction)

I sort of lean towards the private member function convention, additionally because you could pass in parameters to customize the query.

Any thoughts on this?

Answer 1

I almost never pass conditions to find. Usually those conditions would be valuable to add to your object model in the form of a named_scope or even just a class method on the model. Named scopes are nice because you can chain them, which takes a little bit of the bite out of the complexity. They also allow you to pass parameters as well.

Also, you should almost never just pass a raw SQL condition string. You should either use the hash style (:conditions => { :name => 'Pat' }) or the array style (['name = ?', 'Pat']). This way, the SQL is escaped for you, offering some protection against SQL injection attacks.

Finally, I think that the approach you're considering, where you're trying to create conditions in whatever context you're calling find is a flawed approach. It is the job of the model to provide an interface through which the pertinent response is returned. Trying to determine conditions to pass into a find call is too close to the underlying implementation if you ask me. Plus it's harder to test.