sql - Rails - how to convert output of find_by_sql (array) to an ActiveRecord relation?

Question

I am in a need of running a complex SQL query and because I didn't know how to construct the query using ActiveRecod, I had to use a raw SQL query by using find_by_sql:

scope :get_cars, -> do
    find_by_sql('select * from
                  (SELECT cars.*,
                     manufacturer.company_name AS manufacturer_company_name,
                     services.a_num AS service_a_num,
                     (SELECT car_documents.file_url FROM car_documents
                       WHERE car_documents.car_id = cars.id AND car_documents.doc_type = 1 LIMIT 1) AS doc1_file_url,
                     (SELECT car_documents.file_s3_url FROM car_documents
                       WHERE car_documents.cart_id = cars.id AND car_documents.doc_type = 3 LIMIT 1) AS file_inv,
                     (SELECT car_data.demand_lvl FROM car_data
                       WHERE car_data.car_id = cars.id) AS demand_lvl,
                     (SELECT car_logs.invoice FROM car_logs
                       WHERE car_logs.car_id = cars.id AND car_logs.invoice = 1 LIMIT 1) AS invoice_sent
                   FROM "cars"
                   INNER JOIN "services" ON "services"."id" = "cars"."service_id"
                   LEFT JOIN manufacturers ON manufacturers.id = cars.manufacturer_id
                   WHERE (cars.status_id != 6
                          AND cars.delivery_date < NOW() - INTERVAL '15 days'
                    ) ORDER BY cars.pickup_date ASC) t
                 Where doc1_file_url IS NULL OR file_inv IS NULL OR invoice_sent IS NULL')
  end

The output is an array. How do I convert this array into an ActiveRecord object? Or possibly, is there any workaround?

question from:https://stackoverflow.com/questions/65829729/rails-how-to-convert-output-of-find-by-sql-array-to-an-activerecord-relation

Answer 1

I think, You can use the from() method from the Active Record interface. You can use a subquery as a table for a SQL select statement with help of from() method. check the below example. In this way, you will get the active record object.

subquery = Setting.limit(10)
Setting.from("(#{subquery.to_sql}) settings")

In your case,

subquery = "(SELECT cars.*,
                     manufacturer.company_name AS manufacturer_company_name,
                     services.a_num AS service_a_num,
                     (SELECT car_documents.file_url FROM car_documents
                       WHERE car_documents.car_id = cars.id AND car_documents.doc_type = 1 LIMIT 1) AS doc1_file_url,
                     (SELECT car_documents.file_s3_url FROM car_documents
                       WHERE car_documents.cart_id = cars.id AND car_documents.doc_type = 3 LIMIT 1) AS file_inv,
                     (SELECT car_data.demand_lvl FROM car_data
                       WHERE car_data.car_id = cars.id) AS demand_lvl,
                     (SELECT car_logs.invoice FROM car_logs
                       WHERE car_logs.car_id = cars.id AND car_logs.invoice = 1 LIMIT 1) AS invoice_sent
                   FROM "cars"
                   INNER JOIN "services" ON "services"."id" = "cars"."service_id"
                   LEFT JOIN manufacturers ON manufacturers.id = cars.manufacturer_id
                   WHERE (cars.status_id != 6
                          AND cars.delivery_date < NOW() - INTERVAL '15 days'
                    ) ORDER BY cars.pickup_date ASC)"