find_by_sql to something that returns an activeRecords relation

Question

I need find_by_sql to return a ActiveRecord::Relation object. It seems to be not possible. Then I want to write my query in pure ruby.

I have a table with a relationship with itself:

class Alarma < ApplicationRecord
  **belongs_to :root, class_name: "Alarma", foreign_key: "relacionada_id", optional: true
  has_many :children, class_name: "Alarma" , foreign_key: "relacionada_id"**

This is the table:

create_table "alarmas", force: :cascade do |t|
    t.string   "sysname"
    t.string   "component"
    t.string   "details"
    t.integer  "estado_id"
    t.integer  "relacionada_id"
    t.datetime "created_at",     null: false
    t.datetime "updated_at",     null: false
    t.index ["estado_id"], name: "index_alarmas_on_estado_id", using: :btree
    t.index ["severity_id"], name: "index_alarmas_on_severity_id", using: :btree
  end

And this is my query in sql:

  Alarma.find_by_sql("SELECT a1.*  FROM alarmas a1 LEFT OUTER JOIN alarmas a2 ON a1.relacionada_id=a2.id 
WHERE a1.estado_id IN (1,2) OR a2.estado_id IN (1,2)")

The Alarma could be root, and then it has children alarms, or it could be in state: estado_id = 2 what means it is a child, and has a root alarm, (this alarm is relacionada_id).

Example:

Root alarm, relacionada_id = null, id = 99 Child alarm, relacionada_id = 99, id = 112

What I need is the list of alarms, in state estado_id = 1 or 2, and the alarms who his root is in the state estado id 1 or 2 (and his state )

I could get it work with my own sql query using find_by_sql but it returns an array, and I need a ActiveRecord::Relation object because I need to continue working with the result ( because I use a pagination gem and order and page methods don't work with an array)

Answer 1

See Converting an array of objects to ActiveRecord::Relation

The trick is that you take the ids of the results of the raw SQL statement, and perform a second query using where so it returns a ActiveRecord::Relation.

as_array = Alarma.find_by_sql("SELECT a1.*  FROM alarmas a1 LEFT OUTER JOIN alarmas a2 ON a1.relacionada_id=a2.id WHERE a1.estado_id IN (1,2) OR a2.estado_id IN (1,2)")

as_relation = Alarma.where(id: as_array.map(&:id))