SUM of multiple columns with Rails ActiveRecord

When using ActiveRecord, we may think that you are too far from the real thing. It is actually very at implementing actual SQL statements.

What do you to get the SUM of multiple columns using ActiveRecord.

SELECT SUM(qhse+qhse_manager+managing_director+other_internal) FROM "stops"

This is very easy to implement with Rails ActiveRecord

ActiveRecord

@location_ytd_qhse = Stop.sum("qhse+qhse_manager+managing_director+other_internal")

This will produce our nice SQL Statement:

SQL Statement

SELECT SUM(qhse+qhse_manager+managing_director+other_internal) FROM "stops"

If you want to try something else, you can multiply column sum values:

ActiveRecord

@location_ytd_qhse = Stop.sum("qhse*qhse_manager*managing_director*other_internal")

SQL Statement

SELECT SUM(qhse*qhse_manager*managing_director*other_internal) FROM "stops"

 PG::DatatypeMismatch: ERROR: column "column_name" cannot be cast automatically to type integer

Rails has given us a very easy way to create, add columns, change data types on our tables. Everything can’t be done for us. There are still some glitches, especially when there are database specific functionalities.

PG::DatatypeMismatch: ERROR: column “column_name” cannot be cast automatically to type integer
HINT: You might need to specify “USING column_name::integer”.

The above problem shows when you change a string column to an integer column. What’s a Rails programmer going to do? Change the migration.

def up
  change_column :table_name, :column_name, :integer
end
def down
  change_column :table_name, :column_name, :string
end
def up
  change_column :table_name, :column_name, 'integer USING CAST(incident_id AS integer)'
end
def down
  change_column :table_name, :column_name, :string
end

Voila! Fixed!

== 20160812114845 ChangeDataTypeOfColumn: migrating 
-- change_column(:table_name, :column_name, "integer USING CAST(incident_id AS integer)")
   -> 0.0279s
== 20160812114845 ChangeDataTypeOfColumn: migrated (0.0281s)

 Conditionally Chain Scopes in Rails

Scopes are ways to easily retrieve information by creating callable methods on the model. These callable methods are in turn methods that represent queries. These scopes when used return ActiveRecord::Relation that you can chain with other scopes.

class Incident < ActiveRecord::Base
  scope :draft_incidents, -> { where(draft: true)}
  scope :verified_incidents, -> { where(verified: true)}
  scope :this_year, -> {where("EXTRACT(YEAR FROM incidents.incident_date) = ?", Time.now.year)}
end

To chain scopes you can simply concatenate them using dots

Incident.this_year.draft_incidents
[1] pry(main)> Incident.this_year.draft_incidents
  Incident Load (23.6ms)  SELECT "incidents".* FROM "incidents" WHERE "incidents"."deleted_at" IS NULL AND (EXTRACT(YEAR FROM incidents.incident_date) = 2016) AND "incidents"."draft" = $1  [["draft", "t"]]
=> []

To conditionally chain scopes you can do this.

def some_method
  @incidents = Incident.all
  @incidents = @incidents.draft_incidents unless params[:draft].nil?
  @incidents = @incidents.draft_incidents unless params[:this_year].nil?
end

So unless the params are available, you would get all incidents in the table.