ActiveRecord Views

About me

Jan Varljen

Ruby on Rails @ Infinum

CTO @ Productive

 

https://twitter.com/jan_varljen

https://github.com/janvarljen

Case

Basic approach

  • querying multiple tables
    • Person
    • Company
  • no built-in ActiveRecord support
contacts = []
contacts += Person.all
contacts += Company.all

Problems

  • pagination
  • memory usage
  • speed

SQL to the rescue

SELECT
  people.id AS contactable_id,
  'Person' AS contactable_type,
  CONCAT_WS(' ', people.first_name, people.last_name) AS name,
FROM people

UNION

SELECT
  clients.id AS contactable_id,
  'Client' AS contactable_type,
  clients.name AS name,
FROM clients

UNION good parts

  • joining tables in SQL
  • paginatable
  • faster - one SQL query
  • better memory usage

UNION bad parts

  • ActiveRecord unfriendly
  • manual selects (columns, aliases)
  • manual pagination (limit, page)
  • lot of ugly code

Ugly code

def people_sql
  Person.select(
    "people.id AS contact_id, 
    'Person' AS contact_type, 
    CONCAT_WS(' ', people.first_name, people.last_name) AS full_name"
  ).to_sql
end

def companies_sql
  Client.select(
    "clients.id AS contact_id, 
    'Client' AS contact_type, 
    name AS full_name"
  ).to_sql
end

Ugly code

def find
  ActiveRecord::Base.connection.select_all(
    query + " ORDER BY full_name " + limit_sql
  )
end

def query
 [people_sql, companies_sql].join(' UNION ')
end

def limit_sql
  "LIMIT #{per_page} OFFSET #{offset}"
end

def offset
  (page - 1) * per_page
end

ActiveRecord View

  • create SQL View
  • ActiveRecord model for view
  • query that view using the model

Create SQL View

CREATE VIEW contacts AS 

SELECT
  people.id AS contactable_id,
  'Person' AS contactable_type,
  CONCAT_WS(' ', people.first_name, people.last_name) AS name,
FROM people

UNION

SELECT
  clients.id AS contactable_id,
  'Client' AS contactable_type,
  clients.name AS name,
FROM clients

ActiveRecord model

class Contact < ActiveRecord::Base
  belongs_to :contactable, polymorphic: true

  default_scope { alphabetical }

  scope :alphabetical, -> { order(name: :asc) }

  private

  def readonly?
    true
  end
end

Query View

Contact.all
Contact.alphabetical
Contact.first(10)
Contact.paginate(per_page: 10, page: 2)
Contact.where('name LIKE ?', "%#{query}%")

Problems

  • collaboration
  • deployment

Rails Way

  • migrations
  • create/update views on db:migrate

Scenic gem

Create View SQL

  • db/views/contacts_v01.sql
SELECT
  people.id AS contactable_id,
  'Person' AS contactable_type,
  CONCAT_WS(' ', people.first_name, people.last_name) AS name,
FROM people

UNION

SELECT
  clients.id AS contactable_id,
  'Client' AS contactable_type,
  clients.name AS name,
FROM clients

Migrate View

  • db/migrate/xxxxxx_create_contacts_view.rb
class CreateContactsView < ActiveRecord::Migration
  def change
    create_view :contacts
  end
end

Update View SQL

  • db/views/contacts_v02.sql
SELECT
  people.id AS contactable_id,
  'Person' AS contactable_type,
  CONCAT_WS(' ', people.first_name, people.last_name) AS name,
  deleted
FROM people

UNION

SELECT
  clients.id AS contactable_id,
  'Client' AS contactable_type,
  clients.name AS name,
  deleted
FROM clients

Migrate Change

  • db/migrate/xxxxxx_update_contacts_view_to_versions2.rb
class UpdateContactsViewToVersion2 < ActiveRecord::Migration
  def change
    update_view :contacts, version: 2, revert_to_version: 1
  end
end

Scenic good parts

  • easy to use
  • good practices
  • generators

Scenic bad parts

  • MySQL support
  • "early stage of development"
  • not maintained
  • silent dump fail

Alternatives

Made with Slides.com