ActiveRecord execute raw sql not returning any results?

Question

ActiveRecord::Base.connection.execute "SELECT sometable.* from sometable limit 10"
   (76.1ms)  SELECT sometable.* from sometable limit 10
=> #<PG::Result:0x007fbd99647608
 @connection=
  #<PG::Connection:0x007fbd9ac05fa8
   @notice_processor=nil,
   @notice_receiver=nil,
   @socket_io=nil>>

Not sure why no results are returned. There is definitely an active connection and I am able to query the database with normal ActiveRecord query interface. But when I switch to raw sql I get the above problem

Answer 1

Mysql2::Result or PG::Result object has a each method to iterate through results

ActiveRecord::Base.connection.execute("SELECT sometable.* from sometable limit 10").each |row|
  p row
end

Note: You cannot access the results like array. Need to use each method

 array = ActiveRecord::Base.connection.execute("SELECT sometable.* from sometable limit 10")
 array[0] # This will not work

Try select_all if you want results in Array

ActiveRecord::Base.connection.select_all "SELECT sometable.* from sometable limit 10"

Answer 2

What you're seeing is the result returned from a postgresql database: the PostGresResult. (Hence the "PG) The pg gem is how you connect with the database. Here's the wiki for the PG gem. The 'pg' gem doc (and related libpg) gives all of the exciting details about the nitty-gritty of interacting with a Postgresql db.

PG::PGResult is now referred to as PG::Result. The documentation for PG::Result is here.

@shiva is right: For Postgres, the results aren't an Array. (Other databases apparently do return Arrays from these calls). But the PG::Result class has methods for iterating through each row, converting to a Hash, and more. (That's what @shiva was alluding to.) Check out the documentation link above.

You may need to put require 'pg' in your specific source file, depending on how you have your project & development environment set up. (Since you can connect to the database, you already have the pg gem installed.)

Here's the overview and an example from the PG::Result documentation page:

PG::Result (alias = PGResult)

The class to represent the query result tuples (rows). An instance of this class is created as the result of every query. You may need to invoke the #clear method of the instance when finished with the result for better memory performance.

Example:

require 'pg'
conn = PGconn.open(:dbname => 'test')
res  = conn.exec('SELECT 1 AS a, 2 AS b, NULL AS c')
res.getvalue(0,0) # '1'
res[0]['b']       # '2'
res[0]['c']       # nil

Just to illuminate the example a bit:

The example above returns one row (index = 0) with three columns. The first column is 'a'; there is another column called 'b' and the third column is called 'c'. You can get the value for the first column of the first row as:

res.getvalue(0,0)

and it will return the value '1'

You can get the value for the second column of the first row as:

res[0]['b']

and it will return the value '2'

The third line of the example uses the same approach to get the value of column 'c' for the first row (which returns a value of nil).

Of course you can use the more readable methods in PG::Result