Ruby on Rails - Dynamic SQL Query

Question

I execute 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 to add some RoR code to the last line of this request to generate id_code dynamically?

Something like that:

 AND subscriptions.code_id = '@subscription.code' 

Answer 1

You can interpolate Ruby variables in heredoc chains

 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