method

find_by_sql

Importance_3
v3.0.0 - Show latest stable - 2 notes - Class: ActiveRecord::Base
find_by_sql(sql) public

Executes a custom SQL query against your database and returns all the results. The results will be returned as an array with columns requested encapsulated as attributes of the model you call this method from. 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, for example, MySQL specific terms will lock you to using that particular database engine or require you to change your call if you switch engines.

Examples

  # 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", "first_name"=>"Quentin"}>, ...]

  # You can use the same string replacement techniques as you can with ActiveRecord#find
  Post.find_by_sql ["SELECT title FROM posts WHERE author = ? AND created > ?", author_id, start_date]
  > [#<Post:0x36bff9c @attributes={"first_name"=>"The Cheap Man Buys Twice"}>, ...]
Show source
Register or log in to add new notes.
July 1, 2015
1 thank

Space before the opening [

In this example

Post.find_by_sql ["SELECT title FROM posts WHERE author = ? AND created > ?", author_id, start_date]

The array is a parameter, so a space is required before the opening [, which is equivalent to write like this

Post.find_by_sql(["SELECT title FROM posts WHERE author = ? AND created > ?", author_id, start_date])
March 19, 2015
0 thanks

Wrong number of arguments (2 for 1)

If you get this error, wrap brackets with parentheses: Post.find_by_sql([“some query”])