Ruby on Rails - Dynamic SQL Query

Question

I am executing the following raw sql query in one of my controllers :

active_users_query = <<-SQL 
       SELECT count(DISTINCT patients.id)
       FROM public.patients, public.subscriptions, public.users, public.calendar_days
       WHERE patients.user_id = users.id 
       AND patients.id = calendar_days.patient_id 
       AND subscriptions.user_id = patients.user_id 
       AND (date_trunc('day',patients.last_sync) > current_date - interval '30 days' 
       OR date_trunc('day', calendar_days.created_at) > current_date - interval '30 days' 
       OR date_trunc('day',users.current_sign_in_at) > current_date - interval '30 days') 
       AND subscriptions.code_id = 2  
SQL

Is there a way I can add some RoR code to the last line of this query to generate the code_id dynamically ?

Something like this :

AND subscriptions.code_id = '@subscription.code'

dimuch · Accepted Answer · 2012-06-19 16:19:28Z

14

You can interpolate Ruby variables in heredoc strings

active_users_query = <<-SQL 
       SELECT count(DISTINCT patients.id)
       FROM public.patients, public.subscriptions, public.users, public.calendar_days
       WHERE patients.user_id = users.id 
       AND patients.id = calendar_days.patient_id 
       AND subscriptions.user_id = patients.user_id 
       AND (date_trunc('day',patients.last_sync) > current_date - interval '30 days' 
       OR date_trunc('day', calendar_days.created_at) > current_date - interval '30 days' 
       OR date_trunc('day',users.current_sign_in_at) > current_date - interval '30 days') 
       AND subscriptions.code_id = '#{@subscription.code}'  
SQL

answered Jun 19, 2012 at 16:19

dimuch

12.8k2 gold badges41 silver badges23 bronze badges

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

1 Comment

Brainmaniac Over a year ago

Doesn't this make it vulnerable for injections?

Collectives™ on Stack Overflow

Ruby on Rails - Dynamic SQL Query

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