return custom query as an activerecord relation

Question

I wrote this custom query, which I don't think can be converted nicely using active record query interface:

SELECT * FROM dockets 
INNER JOIN entries 
ON entries.docket_id = dockets.id 
WHERE NOT EXISTS 
( 
  SELECT 1 FROM entries AS e 
  WHERE e.docket_id = dockets.id 
  AND e.docket_type REGEXP 'Key1|Key2|Key3|Key4' 
)

I can execute this query using connection.execute:

sql = "SELECT * FROM dockets INNER JOIN entries ON entries.docket_id = dockets.id WHERE NOT EXISTS ( SELECT 1 FROM entries AS e WHERE e.docket_id = dockets.id AND e.docket_type REGEXP 'Key1|Key2|Key3|Key4' )"
Docket.connection.execute(sql)

The problem is does not return an ActiveRecord::Relation. It gives me a MySQL::Result object. Therefore I cannot append relation methods to it:

Docket.connection.execute(sql).limit(10)
NoMethodError: undefined method `limit' for #<Mysql2::Result:0x007fcd1dde6330> 

Is there another interface I can use to return an ActiveRecord::Relation object so I can continue to append relation methods to it? And if not, what other options do I have?

Answer 1

You could do like below:

Docket.joins(:entry).where(Entry.select('1')
     .where("entries.dockect_id = dockets.id")
     .where("entries.docket_type REGEXP 'Key1|Key2|Key3|Key4'")
 .exists.not)