Return ActiveRecord Relation from a raw SQL query using parametrized queries to speed up response

I have a Rails model called VideoEventBundle which contains individual events. This is associated with a VideoMeasurement model via foreign key, and the VideoMeasurement model itself is one of the possible polymorphic child models of Measurement.

SQL schema:

CREATE TABLE public.video_event_bundles (
    id bigint NOT NULL,
    video_measurement_id bigint,
    events jsonb DEFAULT '[]'::jsonb
);

CREATE TABLE public.video_measurements (
    id bigint NOT NULL,
);

CREATE TABLE public.measurements (
    id bigint NOT NULL,
    customer_id bigint NOT NULL,
    actable_type character varying,
    actable_id bigint
);

Now, in the context of an API that primarily returns measurements, I also want to export all video event bundles that belong to the measurements a user has selected/filtered. For that I have a general method that sets @measurements to an ActiveRecord relation based on the user's filters. For instance:

@measurements = Measurement.where(customer_id: params[:customer_id])

To get the actual event bundles, I used to have this query:

    VideoEventBundle.find_by_sql([
      "SELECT video_event_bundles.*
        FROM video_event_bundles
          JOIN video_measurements ON video_event_bundles.video_measurement_id = video_measurements.id
          JOIN measurements ON video_measurements.id = measurements.actable_id
        WHERE measurements.actable_type = 'VideoMeasurement'
        AND measurements.id IN (?)
      ",
      @measurements.select(:id)
    ])

The problem is that this returns an array of objects, and not an ActiveRecord relation. The issue with that is that in the case of unfiltered measurements, that array would be huge. Since the API is paginated, I need to be able to pass limit/offset to that method, and consequently, I have to actually return an ActiveRecord relation.

Now I've seen multiple questions here related to returning that from a raw SQL query:

But I can't manage to use the (?) parameter to actually replace the IDs:

sql = <<-SQL
      SELECT video_event_bundles.*
        FROM video_event_bundles
          JOIN video_measurements ON video_event_bundles.video_measurement_id = video_measurements.id
          JOIN measurements ON video_measurements.id = measurements.actable_id
        AND measurements.id IN (?)
    SQL

    VideoEventBundle.select("*").from("(#{sql}) AS video_event_bundles", @measurements.select(:id))

This raises the error:

ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near ")"
LINE 5:         AND measurements.id IN (?)

I guess I can't work my way around this by starting the query with @measurements.join(…)?

Solution

It should be possible to just use:

VideoEventBundle
  .joins(video_measurement: :measurement)
  .where(measurement: { id: @measurements.select(:id) })

If not we can definitely accomplish your needed SQL using AR's builtin mechanisms and a little bit of Arel:

measurement_table = Measurement.arel_table
vm_table = VideoMeasurement.arel_table
measurement_join = measurement_table.join(vm_table)
   .on(vm_table[:id].eq(measurement_table[:actable_id])
         .and(measurement_table[:actable_type].eq('VideoMeasurement')))
VideoEventBundle
  .joins(:video_measurement)
  .joins(measurement_join)
  .where(measurements: {id: @measurements.select(:id)})

This will result in

SELECT
    video_event_bundles.*
FROM 
    video_event_bundles
    INNER JOIN video_measurements 
      ON video_event_bundles.video_measurement_id = video_measurements.id
    INNER JOIN measurements 
      ON video_measurements.id = measurements.actable_id 
         AND measurements.actable_type = 'VideoMeasurement'
WHERE 
   measurements.id IN (
     SELECT 
        measurements.id
     FROM 
        measurements
     WHERE
        measurements.customer_id = 1234
   )