rails ActiveRecord union activerecord4 SQL psql ruby

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 simply execute your SQL.

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:

Executing raw SQL on the base class gives you flexibility so you are not confined to the model’s structure, Task.find_by_sql is useful if you want an array of items, and Task.from is especially useful if you need an ActiveRecord::Relation.

 

Did you like this article? Check out these too.


 

Found this useful? Know how it can be improved? Get in touch and share your thoughts at blog@hocnest.com