Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
branch: master
Fetching contributors…

Cannot retrieve contributors at this time

201 lines (197 sloc) 13.673 kb
# TODO: These should just be a YML fixture.
# From db/migrate/20081120012826_create_states.rb
State.create :name => 'Alabama', :abbreviation => 'AL'
State.create :name => 'Alaska', :abbreviation => 'AK'
State.create :name => 'Arizona', :abbreviation => 'AZ'
State.create :name => 'Arkansas', :abbreviation => 'AR'
State.create :name => 'California', :abbreviation => 'CA'
State.create :name => 'Colorado', :abbreviation => 'CO'
State.create :name => 'Connecticut', :abbreviation => 'CT'
State.create :name => 'Delaware', :abbreviation => 'DE'
State.create :name => 'District of Columbia', :abbreviation => 'DC'
State.create :name => 'Florida', :abbreviation => 'FL'
State.create :name => 'Georgia', :abbreviation => 'GA'
State.create :name => 'Hawaii', :abbreviation => 'HI'
State.create :name => 'Idaho', :abbreviation => 'ID'
State.create :name => 'Illinois', :abbreviation => 'IL'
State.create :name => 'Indiana', :abbreviation => 'IN'
State.create :name => 'Iowa', :abbreviation => 'IA'
State.create :name => 'Kansas', :abbreviation => 'KS'
State.create :name => 'Kentucky', :abbreviation => 'KY'
State.create :name => 'Louisiana', :abbreviation => 'LA'
State.create :name => 'Maine', :abbreviation => 'ME'
State.create :name => 'Maryland', :abbreviation => 'MD'
State.create :name => 'Massachusetts', :abbreviation => 'MA'
State.create :name => 'Michigan', :abbreviation => 'MI'
State.create :name => 'Minnesota', :abbreviation => 'MN'
State.create :name => 'Mississippi', :abbreviation => 'MS'
State.create :name => 'Missouri', :abbreviation => 'MO'
State.create :name => 'Montana', :abbreviation => 'MT'
State.create :name => 'Nebraska', :abbreviation => 'NE'
State.create :name => 'Nevada', :abbreviation => 'NV'
State.create :name => 'New Hampshire', :abbreviation => 'NH'
State.create :name => 'New Jersey', :abbreviation => 'NJ'
State.create :name => 'New Mexico', :abbreviation => 'NM'
State.create :name => 'New York', :abbreviation => 'NY'
State.create :name => 'North Carolina', :abbreviation => 'NC'
State.create :name => 'North Dakota', :abbreviation => 'ND'
State.create :name => 'Ohio', :abbreviation => 'OH'
State.create :name => 'Oklahoma', :abbreviation => 'OK'
State.create :name => 'Oregon', :abbreviation => 'OR'
State.create :name => 'Pennsylvania', :abbreviation => 'PA'
State.create :name => 'Rhode Island', :abbreviation => 'RI'
State.create :name => 'South Carolina', :abbreviation => 'SC'
State.create :name => 'South Dakota', :abbreviation => 'SD'
State.create :name => 'Tennessee', :abbreviation => 'TN'
State.create :name => 'Texas', :abbreviation => 'TX'
State.create :name => 'Utah', :abbreviation => 'UT'
State.create :name => 'Vermont', :abbreviation => 'VT'
State.create :name => 'Virginia', :abbreviation => 'VA'
State.create :name => 'Washington', :abbreviation => 'WA'
State.create :name => 'West Virginia', :abbreviation => 'WV'
State.create :name => 'Wisconsin', :abbreviation => 'WI'
State.create :name => 'Wyoming', :abbreviation => 'WY'
# From db/migrate/20090604142844_create_list_representatives.rb
if Rails.env.production?
execute 'create or replace view list_representatives as SELECT people.*,
COALESCE(person_approvals.person_approval_avg, 0) as person_approval_average,
COALESCE(bills_sponsored.sponsored_bills_count, 0) as sponsored_bills_count,
COALESCE(total_rolls.tcalls, 0) as total_roll_call_votes,
CASE WHEN people.party = \'Democrat\' THEN COALESCE(party_votes_democrat.pcount, 0)
WHEN people.party = \'Republican\' THEN COALESCE(party_votes_republican.pcount, 0)
ELSE 0
END as party_roll_call_votes,
COALESCE(most_viewed.view_count, 0) as view_count,
COALESCE(blogs.blog_count, 0) as blog_count,
COALESCE(news.news_count, 0) as news_count
FROM people
LEFT OUTER JOIN (select person_approvals.person_id as person_approval_id,
count(person_approvals.id) as person_approval_count,
avg(person_approvals.rating) as person_approval_avg
FROM person_approvals
GROUP BY person_approval_id) person_approvals
ON person_approval_id = people.id
LEFT OUTER JOIN (select sponsor_id, count(id) as sponsored_bills_count
FROM bills
WHERE bills.session = 111
GROUP BY sponsor_id) bills_sponsored
ON bills_sponsored.sponsor_id = people.id
LEFT OUTER JOIN (SELECT DISTINCT(roll_call_votes.person_id), count(DISTINCT "roll_calls".id) AS tcalls
FROM "roll_calls"
LEFT OUTER JOIN "bills" ON "bills".id = "roll_calls".bill_id
INNER JOIN "roll_call_votes" ON "roll_calls".id = "roll_call_votes".roll_call_id
WHERE roll_call_votes.vote != \'0\' AND bills.session = 111
GROUP BY roll_call_votes.person_id) total_rolls
ON total_rolls.person_id = people.id
LEFT OUTER JOIN (SELECT DISTINCT(roll_call_votes.person_id), count(DISTINCT "roll_calls".id) AS pcount
FROM "roll_calls"
LEFT OUTER JOIN "bills" ON "bills".id = "roll_calls".bill_id
INNER JOIN "roll_call_votes" ON "roll_calls".id = "roll_call_votes".roll_call_id
WHERE ((roll_calls.democratic_position = true AND vote = \'+\') OR (roll_calls.democratic_position = false AND vote = \'-\'))
AND bills.session = 111
GROUP BY roll_call_votes.person_id) party_votes_democrat
ON party_votes_democrat.person_id = people.id
LEFT OUTER JOIN (SELECT DISTINCT(roll_call_votes.person_id), count(DISTINCT "roll_calls".id) AS pcount
FROM "roll_calls"
LEFT OUTER JOIN "bills" ON "bills".id = "roll_calls".bill_id
INNER JOIN "roll_call_votes" ON "roll_calls".id = "roll_call_votes".roll_call_id
WHERE ((roll_calls.republican_position = true AND vote = \'+\') OR (roll_calls.republican_position = false AND vote = \'-\'))
AND bills.session = 111
GROUP BY roll_call_votes.person_id) party_votes_republican
ON party_votes_republican.person_id = people.id
LEFT OUTER JOIN (SELECT page_views.viewable_id,
count(page_views.viewable_id) AS view_count
FROM page_views
WHERE page_views.created_at > current_timestamp - interval \'7 days\' AND
page_views.viewable_type = \'Person\'
GROUP BY page_views.viewable_id
ORDER BY view_count DESC) most_viewed
ON people.id=most_viewed.viewable_id
LEFT OUTER JOIN (SELECT count(commentaries.id) as blog_count, commentaries.commentariable_id
FROM commentaries
WHERE commentaries.date > current_timestamp - interval \'7 days\' AND
commentaries.is_news = \'f\' AND
commentaries.commentariable_type = \'Person\'
GROUP BY commentaries.commentariable_id
ORDER BY blog_count DESC) blogs
ON people.id=blogs.commentariable_id
LEFT OUTER JOIN (SELECT count(commentaries.id) as news_count, commentaries.commentariable_id
FROM commentaries
WHERE commentaries.date > current_timestamp - interval \'7 days\' AND
commentaries.commentariable_type = \'Person\' AND commentaries.is_news = \'t\'
GROUP BY commentaries.commentariable_id
ORDER BY news_count DESC) news
ON people.id=news.commentariable_id
WHERE people.title = \'Rep.\';'
else
execute 'create or replace view list_representatives as SELECT people.*,
COALESCE(person_approvals.person_approval_avg, 0) as person_approval_average,
COALESCE(bills_sponsored.sponsored_bills_count, 0) as sponsored_bills_count,
COALESCE(total_rolls.tcalls, 0) as total_roll_call_votes,
CASE WHEN people.party = \'Democrat\' THEN COALESCE(party_votes_democrat.pcount, 0)
WHEN people.party = \'Republican\' THEN COALESCE(party_votes_republican.pcount, 0)
ELSE 0
END as party_roll_call_votes,
COALESCE(most_viewed.view_count, 0) as view_count,
COALESCE(blogs.blog_count, 0) as blog_count,
COALESCE(news.news_count, 0) as news_count
FROM people
LEFT OUTER JOIN (select person_approvals.person_id as person_approval_id,
count(person_approvals.id) as person_approval_count,
avg(person_approvals.rating) as person_approval_avg
FROM person_approvals
GROUP BY person_approval_id) person_approvals
ON person_approval_id = people.id
LEFT OUTER JOIN (select sponsor_id, count(id) as sponsored_bills_count
FROM bills
WHERE bills.session = 111
GROUP BY sponsor_id) bills_sponsored
ON bills_sponsored.sponsor_id = people.id
LEFT OUTER JOIN (SELECT DISTINCT(roll_call_votes.person_id), count(DISTINCT "roll_calls".id) AS tcalls
FROM "roll_calls"
LEFT OUTER JOIN "bills" ON "bills".id = "roll_calls".bill_id
INNER JOIN "roll_call_votes" ON "roll_calls".id = "roll_call_votes".roll_call_id
WHERE roll_call_votes.vote != \'0\' AND bills.session = 111
GROUP BY roll_call_votes.person_id) total_rolls
ON total_rolls.person_id = people.id
LEFT OUTER JOIN (SELECT DISTINCT(roll_call_votes.person_id), count(DISTINCT "roll_calls".id) AS pcount
FROM "roll_calls"
LEFT OUTER JOIN "bills" ON "bills".id = "roll_calls".bill_id
INNER JOIN "roll_call_votes" ON "roll_calls".id = "roll_call_votes".roll_call_id
WHERE ((roll_calls.democratic_position = true AND vote = \'+\') OR (roll_calls.democratic_position = false AND vote = \'-\'))
AND bills.session = 111
GROUP BY roll_call_votes.person_id) party_votes_democrat
ON party_votes_democrat.person_id = people.id
LEFT OUTER JOIN (SELECT DISTINCT(roll_call_votes.person_id), count(DISTINCT "roll_calls".id) AS pcount
FROM "roll_calls"
LEFT OUTER JOIN "bills" ON "bills".id = "roll_calls".bill_id
INNER JOIN "roll_call_votes" ON "roll_calls".id = "roll_call_votes".roll_call_id
WHERE ((roll_calls.republican_position = true AND vote = \'+\') OR (roll_calls.republican_position = false AND vote = \'-\'))
AND bills.session = 111
GROUP BY roll_call_votes.person_id) party_votes_republican
ON party_votes_republican.person_id = people.id
LEFT OUTER JOIN (SELECT page_views.viewable_id,
count(page_views.viewable_id) AS view_count
FROM page_views
WHERE page_views.created_at > current_timestamp - interval \'128 days\' AND
page_views.viewable_type = \'Person\'
GROUP BY page_views.viewable_id
ORDER BY view_count DESC) most_viewed
ON people.id=most_viewed.viewable_id
LEFT OUTER JOIN (SELECT count(commentaries.id) as blog_count, commentaries.commentariable_id
FROM commentaries
WHERE commentaries.date > current_timestamp - interval \'128 days\' AND
commentaries.is_news = \'f\' AND
commentaries.commentariable_type = \'Person\'
GROUP BY commentaries.commentariable_id
ORDER BY blog_count DESC) blogs
ON people.id=blogs.commentariable_id
LEFT OUTER JOIN (SELECT count(commentaries.id) as news_count, commentaries.commentariable_id
FROM commentaries
WHERE commentaries.date > current_timestamp - interval \'128 days\' AND
commentaries.commentariable_type = \'Person\' AND commentaries.is_news = \'t\'
GROUP BY commentaries.commentariable_id
ORDER BY news_count DESC) news
ON people.id=news.commentariable_id
WHERE people.title = \'Rep.\';'
end
Jump to Line
Something went wrong with that request. Please try again.