CODEWITHSUNDEEP
ruby-on-railspostgresql
Dan
Dan

Reputation: 1246

Rails query with Postgres variable usage

I'm trying to create a Rails Postgres query which reuses variables.

A video has many activities(think statistics). Each activity records a video watched and the progress of the video viewed(0-100)%

To generate a video report table which can be paginated I need to query to get all video watching activities and for each video get a) how many views total b) how many views had a progress(integer) that was greater or equal to 90% c) compute the percentage of those who viewed more than 90% e.g. (b/a)*100

The below code is roughly how it should work ( a combination of real and sudo code)

  Video.joins(:activities)
       .where('occurred_at >= ? AND occurred_at <= ?', @start_time, @end_time)
       .select('videos.*, COUNT(activities.id) as total_views, COUNT(activities.progress >= 90) as completed_views, case when total_views = 0 then 0.0 else ROUND( 100.0 *(completion_views/ cast(total_views as float)) ) end as percent_completed')
       .group('videos.id')
       .page(@page).per(@per_page)

How can I reuse the variables total_views and completed_views in the select query to create the percent_completed variable and make they query work. I also realise the calculation COUNT(activities.progress >= 90) as completed_views is not correct either and I'm not sure how to do this.

Upvotes: 0

Views: 664

Answers (1)

Sean Hill
Sean Hill

Reputation: 15056

You need to use the above query in a subquery. 

subquery = Video.joins(:activities)
     .where('occurred_at >= ? AND occurred_at <= ?', @start_time, @end_time)
     .select('videos.*, COUNT(activities.id) as total_views, COUNT(activities.progress >= 90) as completed_views')
     .group('videos.id').as('videos')

Video
  .select('videos.*, case when total_views = 0 then 0.0 else ROUND( 100.0 *(completion_views/ cast(total_views as float)) ) end as percent_completed')
  .from(subquery).page(@page).per(@per_page)

(I'm just using the pseudocode that you provided above, but the general idea is solid.)

Upvotes: 1

Related Questions