def self.visible_for user
  groups       = Arel::Table.new(:groups)
  projects     = Arel::Table.new(:projects)
  roles        = Arel::Table.new(:roles)
  user_roles   = Arel::Table.new(:roles_users)
  roles2       = Arel::Table.new(:roles).alias
  user_roles2  = Arel::Table.new(:roles_users).alias

  join_roles = groups.create_on \
    groups[:id].eq(roles[:resource_id])
    .and(roles[:resource_type].eq('Group'))
  outer_join_roles = projects.create_join(roles, join_roles, Arel::Nodes::OuterJoin)

  join_user_roles = roles.create_on(user_roles[:role_id].eq(roles[:id]))
  outer_join_user_roles = projects.create_join(user_roles, join_user_roles, Arel::Nodes::OuterJoin)

  join_projects  = groups.create_on \
    groups[:project_id].eq(projects[:id])
  outer_join_projects = groups.create_join(projects, join_projects, Arel::Nodes::OuterJoin)

  join_roles2 = groups.create_on \
    projects[:id].eq(roles2[:resource_id])
    .and(roles2[:resource_type].eq('Project'))
  outer_join_roles2 = groups.create_join(roles2, join_roles2, Arel::Nodes::OuterJoin)

  join_user_roles2 = roles2.create_on(user_roles2[:role_id].eq(roles2[:id]))
  outer_join_user_roles2 = groups.create_join(user_roles2, join_user_roles2, Arel::Nodes::OuterJoin)

  uniq.
    joins(outer_join_roles).
    joins(outer_join_user_roles).
    joins(outer_join_projects).
    joins(outer_join_roles2).
    joins(outer_join_user_roles2).
    where(user_roles[:user_id].eq(user.id).or(user_roles2[:user_id].eq(user.id)))
end

def self.visible_for user
  sql = <<EOF
    SELECT DISTINCT groups.*
    FROM groups

    LEFT OUTER JOIN projects ON groups.project_id = projects.id

    LEFT OUTER JOIN roles ON groups.id = roles.resource_id
                          AND roles.resource_type = 'Group'
    LEFT OUTER JOIN roles_users ON roles_users.role_id = roles.id

    LEFT OUTER JOIN roles roles_2 ON projects.id = roles_2.resource_id
                                  AND roles_2.resource_type = 'Project'
    LEFT OUTER JOIN roles_users roles_users_2 ON roles_users_2.role_id = roles_2.id

    WHERE groups.deleted_at IS NULL
      AND (roles_users.user_id = ? OR roles_users_2.user_id = ?)
EOF
  where([sql, user.id, user.id])
end

module ActiveRecord::SecureToken::ClassMethods
  def generate_unique_secure_token(attribute)
    10.times do |i|
      SecureRandom.hex(12).tap do |token|
        if exists?(attribute => token)
          raise "Couldn't generate a unique token in 10 attempts!" if i == 9
        else
          return token
        end
      end
    end
  end
end

module ActiveRecord::SecureToken::ClassMethods

  def generate_unique_secure_token
    SecureRandom.base58(24)
  end

end

ALTER TABLE my_table ADD COLUMN uid text NOT NULL DEFAULT uuid_generate_v4();

thread.increment!(:comment_sequence)
comment.scoped_id = thread.comment_sequence

ALTER TABLE comments ADD COLUMN sequence_id SERIAL;

comment_ids = Comment.pluck(:id).shuffle[0..4]
Comment.where(id: comment_ids)

select * from comments
where id in (
  select round(random() * (select count(1) from logs)::integer)::integer as id
  from generate_series(1, 20)
  group by id -- Discard duplicates
)
limit 10

class Comment < ActiveRecord::Base  
  belongs_to :post, counter_cache: true
end

class Post < ActiveRecord::Base  
end

```
CREATE TABLE posts (  
  id              serial PRIMARY KEY,
  comments_count  integer NOT NULL DEFAULT 0
);

CREATE TABLE comments (  
  id       serial PRIMARY KEY,
  post_id  integer NOT NULL REFERENCES posts(id),
);
```

comment = Comment.create(post: post)

CREATE TRIGGER update_post_comments_count  
  AFTER DELETE OR INSERT OR UPDATE ON comments
  FOR EACH ROW EXECUTE PROCEDURE counter_cache('posts', 'comments_count', 'post_id');

CREATE FUNCTION counter_cache()  
  RETURNS trigger AS $$
    DECLARE
      table_name text    := quote_ident(TG_ARGV[0]);
      counter_name text  := quote_ident(TG_ARGV[1]);
      fk_name text       := quote_ident(TG_ARGV[2]);
      fk_changed boolean := false;
      fk_value integer;
      record record;
    BEGIN
      IF TG_OP = 'UPDATE' THEN
        record := NEW;
        EXECUTE 'SELECT ($1).' || fk_name || ' != ' || '($2).' || fk_name
        INTO fk_changed
        USING OLD, NEW;
      END IF;

      IF TG_OP = 'DELETE' OR fk_changed THEN
        record := OLD;
        EXECUTE 'SELECT ($1).' || fk_name INTO fk_value USING record;
        PERFORM increment_counter(table_name, counter_name, fk_value, -1);
      END IF;

      IF TG_OP = 'INSERT' OR fk_changed THEN
        record := NEW;
        EXECUTE 'SELECT ($1).' || fk_name INTO fk_value USING record;
        PERFORM increment_counter(table_name, counter_name, fk_value, 1);
      END IF;

      RETURN record;
    END;
  $$ LANGUAGE plpgsql;

# Highest Scoring Talks in PA by Authors named Parker

Feedback.joins(talk: [:author, { club: :city }] )
        .select("feedbacks.talk_id, avg(feedbacks.score) as overall_score")
        .where("cities.state_abbr = ?", "PA")
        .where("authors.name LIKE '%?%'", "Parker")
        .group("feedbacks.talk_id")
        .order("overall_score desc")
        .limit(10)
# 665ms

class CreateTalkView < ActiveRecord::Migration
  def up
    connection.execute <<-SQL
      CREATE VIEW v_talks_report AS
        SELECT cities.id as city_id,
               cities.name as city_name,
               cities.state_abbr as state_abbr,
               technologies.id as technology_id,
               clubs.id as club_id,
               clubs.name as club_name,
               talks.id as talk_id,
               talks.name as talk_name,
               authors.id as author_id,
               authors.name as author_name,
               feedback_agg.overall_score as overall_score
        FROM (
          SELECT talk_id, avg(score) as overall_score
          FROM feedbacks
          GROUP BY talk_id
        ) as feedback_agg
        INNER JOIN talks ON feedback_agg.talk_id = talks.id
        INNER JOIN authors ON talks.author_id = authors.id
        INNER JOIN clubs ON talks.club_id = clubs.id
        INNER JOIN cities ON clubs.city_id = cities.id
        INNER JOIN technologies ON clubs.technology_id = technologies.id
    SQL
  end

  def down
    connection.execute 'DROP VIEW IF EXISTS v_talks_report'
  end
end

class TalkReport < ActiveRecord::Base
  # Use associations just like any other ActiveRecord object
  belongs_to :author
  belongs_to :talk
  belongs_to :club
  belongs_to :city
  belongs_to :technology

  # take advantage of talks has_many relationship
  delegate :feedbacks, to: :talk

  self.table_name = 'v_talks_report'

  # views cannot be changed since they are virtual
  def readonly
    true
  end
end

# Highest Scoring Talks in PA by Authors named Parker

TalkReport.where(state_abbr: 'PA')
          .where("author_name LIKE '%Parker%'")
          .order(overall_score: :desc)
          .limit(10)

class CreateTalkView < ActiveRecord::Migration
  def up
    connection.execute <<-SQL
      CREATE MATERIALIZED VIEW mv_talks_report AS
      # [...]
    end
  end
end

class TalkReport < ActiveRecord::Base
  # [...]

  def self.repopulate
    connection.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY #{table_name}")
  end
end