Skip to content

Instantly share code, notes, and snippets.

@jasoncodes

jasoncodes/create_item_children_count.rb

Created October 23, 2011 19:04
Download ZIP

Revisions

  1. jasoncodes revised this gist Oct 23, 2011. No changes.
  2. jasoncodes created this gist Oct 23, 2011.
    15 changes: 15 additions & 0 deletions create_item_children_count.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,15 @@
    class CreateItemChildrenCountView < ActiveRecord::Migration
    def self.up
    execute <<-SQL
    CREATE VIEW item_children_count AS
    SELECT parent_id AS item_id, COUNT(*) as children_count
    FROM items GROUP BY parent_id;
    SQL
    end

    def self.down
    execute <<-SQL
    DROP VIEW item_children_count;
    SQL
    end
    end
    19 changes: 19 additions & 0 deletions item.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,19 @@
    class Item < ActiveRecord::Base
    class ChildrenCountView < ActiveRecord::Base
    set_table_name :item_children_count
    set_primary_key :item_id
    belongs_to :item
    def readonly?
    true
    end
    end
    has_one :children_count_view, :class_name => 'ChildrenCountView', :readonly => true
    scope :with_children, joins(:children_count_view).where('item_children_count.children_count > 0')

    def children_count
    children_count_view.andand.children_count || 0
    end
    def children?
    children_count > 0
    end
    end
    42 changes: 42 additions & 0 deletions postgresql_views.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,42 @@
    class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
    def tables(name = nil)
    query(<<-SQL, name).map { |row| row[0] } +
    SELECT tablename
    FROM pg_tables
    WHERE schemaname = ANY (current_schemas(false))
    SQL
    query(<<-SQL, name).map { |row| row[0] }
    SELECT viewname
    FROM pg_views
    WHERE schemaname = ANY (current_schemas(false))
    SQL
    end

    def table_exists?(name)
    name = name.to_s
    schema, table = name.split('.', 2)

    unless table # A table was provided without a schema
    table = schema
    schema = nil
    end

    if name =~ /^"/ # Handle quoted table names
    table = name
    schema = nil
    end

    query(<<-SQL).first[0].to_i > 0 ||
    SELECT COUNT(*)
    FROM pg_tables
    WHERE tablename = '#{table.gsub(/(^"|"$)/,'')}'
    #{schema ? "AND schemaname = '#{schema}'" : ''}
    SQL
    query(<<-SQL).first[0].to_i > 0
    SELECT COUNT(*)
    FROM pg_views
    WHERE viewname = '#{table.gsub(/(^"|"$)/,'')}'
    #{schema ? "AND schemaname = '#{schema}'" : ''}
    SQL
    end
    end