Adding custom SELECT in ActiveRecord 3

Question

I am trying to use the result of a custom query (a distance between two geographical points) as a model attribute. The end goal is to be able to filter and order rows using that column, e.g. :

Model.with_distance(point).order(:distance).first

I initially thought I could use select in a scope ; here is a shorter version without sanitization :

scope :with_distance, ->(point) { select("ST_DISTANCE(models.point_in_model, ST_GeographyFromText('#{point.to_s}')) AS distance") }

However, that scope only selects the distance column, without retrieving model attributes :

> Model.with_distance(point)
[#<Model >, #<Model >,...]

Adding models.* to the select call solves the problem, at the cost of forcing the retrieval of the entire row in each query and ignoring other select calls.

How can I add a SELECT clause instead of replacing all of them?

Answer 1

Except for the default SELECT models.*, ActiveRecord normally adds SELECT clauses, instead of replacing them.

> Project.select(:id).select(:name)
=> Project Load (0.5ms)  SELECT id, name FROM "projects"

Obviously, this doesn't help when you want "the default SELECT, plus any others I ask for."

You might try a select_star scope

scope :select_star, -> { select("models.*") }
scope :select_star, -> { select(self.arel_table[Arel.star]) } # Arel version, if feeling adventurous

which you could use in places where you want all the columns?

> Model.with_distance(point).select_star
=> SELECT ST_DISTANCE(models.point_in_model, ST_GeographyFromText('0,0')) AS distance, models.* FROM ...

Answer 2

I have the same issue with select behaviour. Let's conclude these bad spots:

• by default, when query is being constructed and we never run select before, the Arel.star is used to fetch all columns.
• Some times we don't want ActiveRecord/ARel to add "*", ex. we are building some intermediate query or query for statistics, and that star just increases a work on our database (and can also affect the ruby callbacks that run on presence of attributes etc).

I think one of solutions (tested on Rails 4.2.4, that also can be easily applied for Rails 3) is to add the following code to your model or ActiveRecord::Base:

app/models/item.rb

...
# include "items".* by default
default_scope ->{select(arel_table[Arel.star])}
# when we dont want to use all columns
scope :no_columns, ->{except(:select)}
...

Now, for example we have the following scope in the app/models/item.rb

...
scope :some_business_logic_scope, ->{
    # doing some big query
    joins(...).
    where(...).
    select(...) # some virtual attributes
    # we dont need to run the following line any more
    # select(self.arel_table[Arel.star])
}
...

Usage

Item.select(:id) # selects all columns
Item.no_columns.select(:id) # selects only column id
Item.no_columns.some_business_logic_scope # selects only required columns
Item.some_business_logic_scope # selects all attributes including those added by some_business_logic_scope