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:

Depending on your needs, you may want to use either of these methods. 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. 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