Union with ActiveRecord 4 & Postgres

Writing the query

def accessible_tasks_query
  <<~SQL
    SELECT tasks.* FROM tasks
      INNER JOIN ...
      WHERE ...

    UNION

    SELECT tasks.* FROM tasks
      INNER JOIN Something Else...
      WHERE ...
  SQL
end

Executing SQL

PG Result

If a PG::Result return type satisfies your needs then you can execute your SQL like so:

ActiveRecord::Base.connection.execute(accessible_tasks_query)
#=> <PG::Result:0x007ffc57365e88 status=PGRES_TUPLES_OK ntuples=112480 nfields=27 cmd_tuples=112480>

Array

Want an array of items?

Using the find_by_sql class method will return just that.

Task.find_by_sql accessible_tasks_query
#=> [#<Task:...>, <Task:...>]

ActiveRecord::Relation

Using the Active Record’s class method from will return an ActiveRecord::Relation.

Task.from accessible_tasks_query
#=> #<Task::ActiveRecord_Relation:

Takeaway

Use ActiveRecord::Base to execute raw SQL, Model.find_by_sql if you want an array of activerecord objects, or Model.from if you need an ActiveRecord::Relation.

 

Did you like this article? Check out these too.

• Rails 5 RabbitMQ Bunny Setup
• Lossy Mode with ActiveRecord and Postgres

---

 

Found this useful? Have a suggestion? Get in touch at blog@hocnest.com.