ActiveRecord vs SQL raw queries? [closed]

Question

What is the difference between ActiveRecord and writing raw queries.?? In which situation one should adopt writing raw queries in rails app .

Answer 1

The key difference is that ActiveRecord lets you be more productive by using a higher level abstraction which lets you write less code.

It also makes your code more portable by abstracting away some of the differences between RDBMS's.

This abstraction comes at a cost and can lead to performance problems if you don't pay attention to the queries that ActiveRecord generates.

In which situation one should adopt writing raw queries in rails app?

When needed to accomplish the job at hand. This could for example be to use the Postgres extension ILIKE:

# case insensitive search:
@user = User.where('users.name ILIKE ?', params[:name])

Or when you need to execute a complicated join or another performance critical operation. Like this example which fetches a number of associated records per parent. It sounds simple but is extremely difficult to do in ActiveRecord.

class Observation < ActiveRecord::Base
  belongs_to :station

  # ...

  def self.pluck_from_each_station(limit = 1)
    ActiveRecord::Base.connection.execute(%Q{
      SELECT o.id
      FROM   stations s
      JOIN LATERAL (
         SELECT id, created_at
         FROM   observations
         WHERE  station_id = s.id  -- lateral reference
         ORDER  BY created_at DESC
         LIMIT  #{limit}
      ) o ON TRUE
      ORDER BY s.id, o.created_at DESC;
    }).field_values('id')
  end
end

And lastly if you don't know how to perform an operation with ActiveRecord it is often better to write a bit of SQL with a test backing it and go back and refactor later.

@users = User.where("occupation NOT IN(?)", params[:blacklist])) # @todo fix

You can then refactor this to:

@users.where.not(occupation: params[:blacklist])