20131118172653_create_transactional_items_view.rb

# db/migrate/20131118172653_create_transactional_items_view.rb
class CreateTransactionalItemsView < ActiveRecord::Migration
  def up
    select_sql = File.open("#{Rails.root}/db/migrate/20131118172653_create_transactional_items_view.sql", 'r') { |f| f.read } 
    
    # for materialized view:
    view_sql = "CREATE MATERIALIZED VIEW transactional_items AS (#{select_sql})"
    # for normal view:
    view_sql = "CREATE VIEW transactional_items AS (#{select_sql})"
    
    execute view_sql.gsub(/\s+/, " ").strip
  end


  def down
    execute "DROP VIEW transactional_items"
  end
end

20131118172653_create_transactional_items_view.sql

-- db/migrate/20131118172653_create_transactional_items_view.sql
-- do some "crazy" stuff in your view - in this case use union to join tables
(
  SELECT NULL::integer AS id,
    'Transaction' AS source_type,
    transactions.id AS source_id,
    accounts.user_id,
    transactions.date,
    transactions.amount,
    transactions.direction,
    'closed' AS state
  FROM transactions
    INNER JOIN accounts ON accounts.id = transactions.account_id
) UNION (
  SELECT NULL::integer AS id,
    'RecurringTransaction' AS source_type,
    recurring_transactions.id AS source_id,
    accounts.user_id,
    recurring_transactions.date,
    recurring_transactions.amount,
    recurring_transactions.direction,
    recurring_transactions.state
  FROM recurring_transactions
    INNER JOIN accounts ON accounts.id = recurring_transactions.account_id
) UNION (
  SELECT NULL::integer AS id,
    'Budget' AS source_type,
    budgets.id AS source_id,
    budgets.user_id,
    budgets.start_date AS date,
    budgets.amount,
    budgets.direction,
    budgets.state
  FROM budgets
)

README.md

== Usage

>> TransactionalItem.refresh! # manually refresh (recalculate) materialized view
>> user = User.first
>> user.transactional_items.positive.this_month # your incomes for this month
+----+-------------+-----------+---------+------------+---------+-----------+--------+
| id | source_type | source_id | user_id | date       | amount  | direction | state  |
+----+-------------+-----------+---------+------------+---------+-----------+--------+
|    | Transaction | 2831      | 1       | 2013-12-01 | 10000.0 | income    | closed |
|    | Transaction | 2920      | 1       | 2013-12-10 | 800.0   | income    | closed |
|    | Transaction | 2844      | 1       | 2013-12-02 | 30000.0 | income    | closed |
+----+-------------+-----------+---------+------------+---------+-----------+--------+

>> user.transactional_items.negative.future # your planned outcomes
+----+----------------------+-----------+---------+------------+---------+-----------+--------+
| id | source_type          | source_id | user_id | date       | amount  | direction | state  |
+----+----------------------+-----------+---------+------------+---------+-----------+--------+
|    | RecurringTransaction | 983       | 1       | 2014-10-08 | -100.0  | expense   | opened |
|    | RecurringTransaction | 951       | 1       | 2014-02-08 | -1750.0 | expense   | opened |
|    | RecurringTransaction | 967       | 1       | 2014-06-08 | -1890.0 | expense   | opened |
|    | RecurringTransaction | 955       | 1       | 2014-06-08 | -1750.0 | expense   | opened |
|    | RecurringTransaction | 968       | 1       | 2014-07-08 | -1890.0 | expense   | opened |
...

>> user.transactional_items.past.negative.stats # total stats for your outcomes
+----+------------+-------+
| id | sum        | count |
+----+------------+-------+
|    | -263554.37 | 126   |
+----+------------+-------+
1 row in set

>> user.transactional_items.past.negative.monthly_stats # total stats for your outcomes by month
+----+-----------+-------+------+-------+
| id | sum       | count | year | month |
+----+-----------+-------+------+-------+
|    | -15000.0  | 2     | 2013 | 1     |
|    | -5000.0   | 2     | 2013 | 2     |
|    | -4350.0   | 1     | 2013 | 3     |
|    | -4000.0   | 1     | 2013 | 4     |
|    | -14000.0  | 2     | 2013 | 5     |
|    | -17991.0  | 6     | 2013 | 6     |
|    | -15524.0  | 6     | 2013 | 7     |
|    | -22922.0  | 10    | 2013 | 8     |
|    | -73756.4  | 13    | 2013 | 9     |
|    | -36031.67 | 35    | 2013 | 10    |
|    | -23898.1  | 27    | 2013 | 11    |
|    | -31081.2  | 21    | 2013 | 12    |
+----+-----------+-------+------+-------+
12 rows in set

scopes.rb

# app/models/transaction/scopes.rb
module Transaction::Scopes
  extend ActiveSupport::Concern

  included do
    scope :zero,        -> { where("#{table_name}.amount = 0.0") }
    scope :positive,    -> { where("#{table_name}.amount > 0.0") }
    scope :negative,    -> { where("#{table_name}.amount < 0.0") }
    scope :non_zero,    -> { where("#{table_name}.amount <> 0.0") }
    scope :significant, ->(x=25) { where("(#{table_name}.amount <= -:x) ^ (#{table_name}.amount >= :x)", x: x) }

    scope :future,  ->(date = Date.today) { where("#{table_name}.date >= ?", date) }
    scope :past,    ->(date = Date.today) { where("#{table_name}.date < ?", date) }
    scope :year,    ->(year = Date.today.year) { where("EXTRACT(YEAR FROM date) = ?", year) }
    scope :month,   ->(month = Date.today.month) { where("EXTRACT(MONTH FROM date) = ?", month) }
    scope :between, ->(from, to) { where(date: from..to) }

    scope :this_week,      -> { between(Date.today.at_beginning_of_week, Date.today.at_end_of_week) }
    scope :this_month,     -> { between(Date.today.at_beginning_of_month, Date.today.at_end_of_month) }
    scope :this_quarter,   -> { between(Date.today.at_beginning_of_quarter, Date.today.at_end_of_quarter) }
    scope :this_semester,  -> { between(Date.today.at_beginning_of_semester, Date.today.at_end_of_semester) }
    scope :this_year,      -> { between(Date.today.at_beginning_of_year, Date.today.at_end_of_year) }

    scope :last_week,      ->(from = 1.week.ago.to_date)   { between(from.at_beginning_of_week, from.at_end_of_week) }
    scope :last_month,     ->(from = 1.month.ago.to_date)  { between(from.at_beginning_of_month, from.at_end_of_month) }
    scope :last_quarter,   ->(from = 3.months.ago.to_date) { between(from.at_beginning_of_quarter, from.at_end_of_quarter) }
    scope :last_semester,  ->(from = 6.months.ago.to_date) { between(from.at_beginning_of_semester, from.at_end_of_semester) }
    scope :last_year,      ->(from = 1.year.ago.to_date)   { between(from.at_beginning_of_year, from.at_end_of_year) }

    scope :next_week,      ->(from = 1.week.since.to_date)   { between(from.at_beginning_of_week, from.at_end_of_week) }
    scope :next_month,     ->(from = 1.month.since.to_date)  { between(from.at_beginning_of_month, from.at_end_of_month) }
    scope :next_quarter,   ->(from = 3.months.since.to_date) { between(from.at_beginning_of_quarter, from.at_end_of_quarter) }
    scope :next_semester,  ->(from = 6.months.since.to_date) { between(from.at_beginning_of_semester, from.at_end_of_semester) }
    scope :next_year,      ->(from = 1.year.since.to_date)   { between(from.at_beginning_of_year, from.at_end_of_year) }

    scope :last_7_days,    -> { between(7.days.ago.to_date, Date.today) }
    scope :last_14_days,   -> { between(14.days.ago.to_date, Date.today) }
    scope :last_30_days,   -> { between(30.days.ago.to_date, Date.today) }
    scope :last_60_days,   -> { between(60.days.ago.to_date, Date.today) }
    scope :last_90_days,   -> { between(90.days.ago.to_date, Date.today) }
    scope :last_180_days,  -> { between(180.days.ago.to_date, Date.today) }
    scope :last_270_days,  -> { between(270.days.ago.to_date, Date.today) }
    scope :last_52_weeks,  -> { between(52.weeks.ago.end_of_week.to_date, Date.today) }
    scope :last_12_months, -> { between(12.months.ago.end_of_month.to_date, Date.today) }

    scope :next_7_days,    -> { between(1.day.since.to_date, 7.days.since.to_date) }
    scope :next_14_days,   -> { between(1.day.since.to_date, 14.days.since.to_date) }
    scope :next_30_days,   -> { between(1.day.since.to_date, 30.days.since.to_date) }
    scope :next_60_days,   -> { between(1.day.since.to_date, 60.days.since.to_date) }
    scope :next_90_days,   -> { between(1.day.since.to_date, 90.days.since.to_date) }
    scope :next_180_days,  -> { between(1.day.since.to_date, 180.days.since.to_date) }
    scope :next_270_days,  -> { between(1.day.since.to_date, 270.days.since.to_date) }
    scope :next_52_weeks,  -> { between(1.day.since.to_date, 52.weeks.since.end_of_week.to_date) }
    scope :next_12_months, -> { between(1.day.since.to_date, 12.months.since.end_of_month.to_date) }

    scope :with_extracted_year,      -> { select("EXTRACT(YEAR FROM date) AS year") }
    scope :with_extracted_month,     -> { select("EXTRACT(MONTH FROM date) AS month") }
    scope :with_extracted_day,       -> { select("EXTRACT(DAY FROM date) AS day") }
    scope :with_extracted_week,      -> { select("EXTRACT(WEEK FROM date) AS week") }
    scope :with_extracted_quarter,   -> { select("EXTRACT(QUARTER FROM date) AS quarter") }
    scope :with_extracted_semester,  -> { select("CASE WHEN EXTRACT(MONTH FROM date) > 6 THEN 2 ELSE 1 END AS semester") }
    scope :with_extracted_date,      -> { with_extracted_year.with_extracted_month.with_extracted_day }

    scope :stats,            -> { select("SUM(amount) AS sum, COUNT(amount) AS count") }
    scope :advanced_stats,   -> { select("AVG(amount) AS avg, STDDEV(amount) AS stddev, MEDIAN(amount) AS median, MODE(amount) AS mode, MIN(amount) AS min, MAX(amount) AS max") }
    scope :annual_stats,     -> { stats.with_extracted_year.group("year").order("year") }
    scope :monthly_stats,    -> { stats.with_extracted_year.with_extracted_month.group("year, month").order("year, month") }
    scope :daily_stats,      -> { stats.with_extracted_date.group("year, month, day").order("year, month, day") }
    scope :weekly_stats,     -> { stats.with_extracted_year.with_extracted_week.group("year, week").order("year, week") }
    scope :quarterly_stats,  -> { stats.with_extracted_year.with_extracted_quarter.group("year, quarter").order("year, quarter") }
    scope :semester_stats,   -> { stats.with_extracted_year.with_extracted_semester.group("year, semester").order("year, semester") }

    scope :last_52_weeks_stats,  -> { last_52_weeks.weekly_stats }
    scope :last_12_months_stats, -> { last_12_months.monthly_stats }
    scope :next_52_weeks_stats,  -> { next_52_weeks.weekly_stats }
    scope :next_12_months_stats, -> { next_12_months.monthly_stats }
  end

end

transactional_item.rb

# app/models/transactional_item.rb
class TransactionalItem < ActiveRecord::Base
  self.table_name = "transactions"
  self.primary_key = :id

  belongs_to :source, polymorphic: true
  belongs_to :user

  # NOTE: scopes are seperated just for clarification
  # in this example there's no need to seperate it but in my app 
  # scopes are used by several models (Transaction, Budget, ...)
  include Transaction::Scopes

  # in MySQL you'd need to use different command
  def self.refresh!
    connection.execute("REFRESH MATERIALIZED VIEW #{table_name} WITH DATA")
  end
  
  def readonly?
    true
  end
end

user.rb

# app/models/user.rb
class User < ActiveRecord::Base
  # ...
  
  has_many :transactional_items
end