instance method `find_by_sql`

Ruby on Rails 6.0.6

Since v3.2.22.5

Available in: v3.2.22.5 v4.0.13 v4.1.16 v4.2.9 v5.2.8.1 v6.0.6 v6.1.7.10 v7.0.10 v7.1.6 v7.2.3 v8.0.4 v8.1.2

Compare with:

find_by_sql(sql, binds = [], preparable: nil, &block)

Executes a custom SQL query against your database and returns all the results. The results will be returned as an array, with the requested columns encapsulated as attributes of the model you call this method from. For example, if you call Product.find_by_sql, then the results will be returned in a Product object with the attributes you specified in the SQL query.

If you call a complicated SQL query which spans multiple tables, the columns specified by the SELECT will be attributes of the model, whether or not they are columns of the corresponding table.

The sql parameter is a full SQL query as a string. It will be called as is; there will be no database agnostic conversions performed. This should be a last resort because using database-specific terms will lock you into using that particular database engine, or require you to change your call if you switch engines.

# A simple SQL query spanning multiple tables
Post.find_by_sql "SELECT p.title, c.author FROM posts p, comments c WHERE p.id = c.post_id"
# => [#<Post:0x36bff9c @attributes={"title"=>"Ruby Meetup", "author"=>"Quentin"}>, ...]

You can use the same string replacement techniques as you can with ActiveRecord::QueryMethods#where:

Post.find_by_sql ["SELECT title FROM posts WHERE author = ? AND created > ?", author_id, start_date]
Post.find_by_sql ["SELECT body FROM comments WHERE author = :user_id OR approved_by = :user_id", { :user_id => user_id }]

Parameters

sql req
binds opt = []
preparable key = nil
block block

Source

# File activerecord/lib/active_record/querying.rb, line 45
    def find_by_sql(sql, binds = [], preparable: nil, &block)
      result_set = connection.select_all(sanitize_sql(sql), "#{name} Load", binds, preparable: preparable)
      column_types = result_set.column_types.dup
      attribute_types.each_key { |k| column_types.delete k }
      message_bus = ActiveSupport::Notifications.instrumenter

      payload = {
        record_count: result_set.length,
        class_name: name
      }

      message_bus.instrument("instantiation.active_record", payload) do
        if result_set.includes_column?(inheritance_column)
          result_set.map { |record| instantiate(record, column_types, &block) }
        else
          # Instantiate a homogeneous set
          result_set.map { |record| instantiate_instance_of(self, record, column_types, &block) }
        end
      end
    end

Defined in activerecord/lib/active_record/querying.rb line 45 · View on GitHub · Improve this page · Find usages on GitHub

Defined in ActiveRecord::Querying

Signature

Parameters