914_sql_check.rb

# app/controllers/admin/article_requests_controller.rb
query = "
  SELECT COUNT(*) AS num_leads_with_tag FROM users
  INNER JOIN taggings ON taggings.tag_id = 24
  AND taggings.taggable_type = 'User'
  AND taggings.taggable_id = users.id
  AND (users.interested_in_writing = 'Yes' OR users.interested_in_writing = 'Maybe')
  AND ((users.last_contacted_at < #{ActiveRecord::Base.sanitize(4.days.ago)}) OR (users.last_contacted_at is null))
"
Tag.find_by_sql(query)


# app/controllers/users_controller.rb
valid_column_names = ['users.id', 'first_name', 'last_name', 'rank_id', 'articles_count', 'users.comments_count', 'users.comments_received', 'users.notes_posted', 'users.notes_received', 'date_last_login', 'permalink']
select = valid_column_names.to_sentence(:connector => nil) + ", if((#{ActiveRecord::Base.sanitize(30.days.ago)} < users.created_at), ' new writers', 'existing writers') age"
User.find(:all, :select => select)


# app/models/article_request.rb
query = "
  SELECT users.* FROM users
  INNER JOIN taggings
  ON taggings.taggable_type = 'User'
  AND taggings.taggable_id = users.id
  AND taggings.tag_id = 24
  AND users.interested_in_writing IS NOT NULL
  AND users.interested_in_writing != 'no'
  AND ((users.last_contacted_at < #{ActiveRecord::Base.sanitize(4.days.ago)}) OR (users.last_contacted_at is null))
  ORDER BY RAND() LIMIT ?
"
User.find_by_sql([query, 100])

lower_bound = "#{ActiveRecord::Base.sanitize(10.days.ago)}"
upper_bound = "#{ActiveRecord::Base.sanitize(7.days.ago)}"
query = "
  SELECT distinct users.* FROM users LEFT OUTER JOIN article_requests
  ON users.id = article_requests.user_id
  WHERE article_requests.id IS NULL
  AND interested_in_writing = 'yes'
  AND users.created_at > #{lower_bound}
  AND users.created_at < #{upper_bound}
"
Lead.find_by_sql(query)


# app/models/tag.rb
Tag.connection.select_all("select 24 tag_id, count(id) member_count, month from (select id, month(created_at) month from taggings where taggable_type = 'User' and tag_id = 24 and created_at > #{ActiveRecord::Base.sanitize(5.months.ago)}) tt group by month order by month desc")

article_count_query = "
  select taggings.tag_id, count(articles.id) article_count, sum(hit_count) total_reads, avg(hit_count) mean_reads, min(hit_count) min_reads, max(hit_count) max_reads 
  from articles inner join taggings
    on articles.id = taggings.taggable_id
    and taggings.tag_id = 24
    and taggings.taggable_type = 'Article'
    and articles.created_at > #{ActiveRecord::Base.sanitize(Date.today-24)} and articles.created_at <= #{ActiveRecord::Base.sanitize(Date.today-24)}
    and hit_count > #{24} and hit_count <= #{24}
    group by tag_id
"
total_author_count_query = "
  select count(distinct owner_id) author_count from taggings
    where created_at > #{ActiveRecord::Base.sanitize(Date.today-24)} and created_at <= #{ActiveRecord::Base.sanitize(Date.today-24)}
    and taggable_type = 'Article'
    and tag_id = 24
"
active_author_count_query = "
  select count(*) active_author_count from (select count(owner_id) c from taggings
    where created_at > #{ActiveRecord::Base.sanitize(Date.today-24)} and created_at <= #{ActiveRecord::Base.sanitize(Date.today-24)}
    and taggable_type = 'Article'
    and tag_id = 24
    group by owner_id having c > 3) tt
"
Tag.connection.select_one(article_count_query)
Tag.connection.select_one(total_author_count_query)
Tag.connection.select_one(active_author_count_query)