Rails query with Postgres variable usage

Question

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.

Sean Hill · Accepted Answer · 2015-05-08 02:01:26Z

1

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.)

answered May 8, 2015 at 2:01

Sean Hill

15.1k2 gold badges52 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dan Over a year ago

This worked - Thanks. Note: I had to rewrite the joins query method in the subquery as Rails uses $1 in the query which results in the error PG::ProtocolViolation: ERROR

Collectives™ on Stack Overflow

Rails query with Postgres variable usage

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related