ActiveRecord is great. It lets you quickly build queries, reuse query code with named scopes, and chain multiple scopes together.
But sometimes you might need to just run some raw SQL. ActiveRecord can make that easier, too. Here are some key methods from the ActiveRecord::ConnectionAdapters::DatabaseStatements
module.
execute
can be used to run a SQL command. If you're using PostgreSQL this will return a PG::Result
object.
result = ActiveRecord::Base.connection.execute("SELECT 'PG Example', CURRENT_DATE, 123")
=> #<PG::Result:0x00007f7f094e14e0 ...
result.values
=> [["PG Example", "2023-04-28", 123]]
result.first # If you don't use aliases, columns can be merged together here
=> {"?column?"=>123, "current_date"=>"2023-04-28"}
result.first['current_date'].class
=> String
If you're using MySQL this will return a Mysql2::Result
object.
result = ActiveRecord::Base.connection.execute("SELECT 'MySQL Example', CURRENT_DATE, 123")
=> #<Mysql2::Result:0x00007fa01bbc1ae8 ...
result.to_a
=> [["MySQL Example", Fri, 28 Apr 2023, 123]]
result.first
=> ["MySQL Example", Fri, 28 Apr 2023, 123]
result.first[1].class
=> Date
Note that both the interface for the result object and the type-casting is slightly different depending on the database.
select_all
provides similar results but uses an ActiveRecord::Result
.
result = ActiveRecord::Base.connection.select_all("SELECT 'abc', 123")
=> #<ActiveRecord::Result:0x00007fa0199a7728 ...
result.rows
=> [["abc", 123]]
select_rows
returns an array of results.
ActiveRecord::Base.connection.select_rows("SELECT 'abc', 123")
=> [["abc", 123]]
ActiveRecord::Base.connection.select_rows("SELECT 'a' AS value UNION SELECT 'b' AS value")
=> [["a"], ["b"]]
select_one
returns the first row of the query result.
ActiveRecord::Base.connection.select_one("SELECT 'a' AS value UNION SELECT 'b' AS value")
=> {"value"=>"a"}
select_values
returns an array of the values of the first column in a select.
ActiveRecord::Base.connection.select_values("
SELECT 'a1' AS value1, 'b1' AS value2
UNION
SELECT 'a2' AS value1, 'b2' AS value2
")
=> ["a1", "a2"]
select_value
returns the first column from the first row in a select.
ActiveRecord::Base.connection.select_value("
SELECT 'a1' AS value1, 'b1' AS value2
UNION
SELECT 'a2' AS value1, 'b2' AS value2
")
=> "a1"
References