Query Objects

[andypike]

I've been thinking about adding a nice way to deal with database queries. Here is my thinking at the moment but I'm looking for feedback and ideas in this area.

At the moment with Rectify users are encouraged to place there database queries within their ActiveRecord models. That's ok because Rectify tries to help developers find a home for their code and so as the models are just for persistence it makes sense.

However, there are problems with this approach:

1. As a project matures, the number of queries will increase and so there is a danger that models will end up having high churn and continue to grow as the number of queries grow.
2. As a project increases in complexity, the queries may well increase in complexity as well. By that I mean that sometimes developers will want to build a query using a SQL string so they can leverage database specific features.

I'd like to think of a nice way to allow developers to use Query Objects which will help reduce the size of their models and also improve the experience when working with plain (potentially large) SQL queries strings. At the same time I don't what to stop or prohibit the use of the ActiveRecord DSL. So we need a nice way to create and use Query Objects where it makes sense.

Maybe the guidance should be that small ActiveRecord DSL style queries and scopes are added to the model and more complex queries should use Query Objects. Not sure what the API should be for this so I'm creating this issue to gather ideas and feedback.

For large SQL strings I was thinking about allowing SQL to be written in a *.sql file and allow parameter placeholders in the style that ActiveRecord has already:

select * from users where username = :username

Then we can use existing ActiveRecord APIs to execute the sql file with safe parameter inclusion (dealing with SQL injection). We might be able to use conventions to find the SQL file based on the Query Object name and the SQL filename. For example, use find_by_sql, here is the example from the docs (http://api.rubyonrails.org/classes/ActiveRecord/Querying.html#method-i-find_by_sql):

Post.find_by_sql([
  "SELECT body FROM comments WHERE author = :user_id OR approved_by = :user_id", 
  { :user_id => user_id }
])

So I'm imagining the Query Object would be able to take parameters, contain logic (if required) to manipulate those parameters for the query, load the sql file and execute it via find_by_sql.

[kimberger]

class Video < ActiveRecord::Base
  scope :featured_and_popular, Videos::FeaturedAndPopularQuery
end  

As described in Delegating to Query Objects through ActiveRecord Scopes. That's a possible implementation?

I assume you can add parameters with:

scope :featured_and_popular, -> category { Videos::FeaturedAndPopularQuery.new(category) }

[andypike]

After taking some time to think about how Query Objects might work, I started experimenting in the example project. There is a PR over there with some initial ideas. Probably needs more explanation but it's a start. Checkout https://github.com/andypike/smarty_pants/pull/3

[andypike]

Merged in #8 which covers this issue.