Rails scope issue

Question

I am trying to add a scope to model. I am looking to calculate total plays from the db. I am on Rails 3.2.13

MYSQL query provides the expected results.

SELECT COUNT(DISTINCT(CONCAT(stats.filename,stats.viewer_id)))
FROM `stats` 
WHERE `stats`.`user_id` = 4 
AND (stats.cf_status in ('Hit', 'Miss', 'RefreshHit', 'Error')) 
AND (date_time between '2013-04-14 05:00:00' and '2013-04-21 16:35:16')

= 169 plays

I am trying to get this scope to work but Rails is ignoring it when I check the logs.

Stats model

scope :only_valid, -> {
where("stats.cf_status in ('Hit', 'Miss', 'RefreshHit', 'Error')")
}
scope :totviews, -> {
where(" COUNT(DISTINCT(CONCAT(stats.filename,stats.viewer_id)))")
}

Dashboard Controller total_views: Stat.for_user(current_user).only_valid.between(@start_at,@end_at).totviews.count,

Output from the logs Still shows the original query not the modified one.

SELECT COUNT(*) 
FROM `stats` 
WHERE `stats`.`user_id` = 4 
AND (stats.cf_status in ('Hit', 'Miss', 'RefreshHit', 'Error')) 
AND (date_time between '2013-04-14 05:00:00' and '2013-04-21 17:17:29')

= 4589 plays

Any help is appreciated. Thanks

expose(:overview_chart_views) {
{
total_views:    to_col(Stat.for_user(current_user).only_valid.between(@start_at, @end_at).by_day),
total_viewers:  to_col(Stat.unique_plays.for_user(current_user).only_valid.between(@start_at, @end_at).by_day),
new_viewers:    to_col(Viewer.for_user(current_user).with_first_views.only_valid.between(@start_at, @end_at).by_day),
return_viewers: to_col(Viewer.for_user(current_user).without_first_views.only_valid.between(@start_at, @end_at).by_day)
}

private

def to_col(results)
lookup = {}
results.each {|row|
if !row['date'].kind_of?(Date)
row['date'] = Date.parse(row['date']);
end
lookup[row['date']] = row['result'].to_i }
(@start_at.to_date..@end_at.to_date).to_a.map {|date| lookup[date] || 0}
end
end

Your scope totviews is trying to perform a count in a where clause. I'm thinking that's not going to work :) — Jeff Paquette, Apr 22 '13 at 18:21

score 0 · Accepted Answer · answered Apr 22 '13 at 18:26

0

The totviews scope is invalid since you're filtering the elements with a count function.

Try removing it and changing the query to:

class Stat < ActiveRecord::Base
  def self.total_views
    Stat.for_user(current_user).only_valid.between(@start_at,@end_at).count('DISTINCT(CONCAT(stats.filename,stats.viewer_id))')
  end
end

answered Apr 22 '13 at 18:26

alf

18,372
10
61
92

Jeff, the filter was still ignored when the where clause was changed back to a select. Alphonso, your fix worked great for the count function. Thanks! One last question. In line two of my overview_chart_views. I am having the same problem. total_viewers: to_col(Stat.unique_plays.for_user(current_user).only_valid.between(@start_at, @end_at).by_day), – kilomo Apr 22 '13 at 19:17
@jnbankston I'm not sure I follow, but the fix would be the same, don't use scopes with count functions. Instead, pass the string argument to `count`. – alf Apr 22 '13 at 19:20
The data in :overview_chart_views is being displayed in HighCharts using backbone. Highcharts is expecting a result grouped by date. The initial question just needed a count. thanks. SELECT date, count(id) as result FROM `stats` WHERE `stats`.`user_id` = 4 AND (stats.cf_status in ('Hit', 'Miss', 'RefreshHit', 'Error')) AND (date_time between '2013-04-14 05:00:00' and '2013-04-21 18:44:22') GROUP BY date ORDER BY result – kilomo Apr 22 '13 at 19:30
@jnbankston probably the scope `by_day` needs to be something like `group(:date)`. If it doesn't solve your problem post a new question with your code. – alf Apr 22 '13 at 19:36

Rails scope issue

1 Answers1