In rails one of the way to execute raw query is use of ActiveRecord::Base.connection.execute

We are going to see if we use this option to execute raw query and we are working with pg database then what result it provides and what operations can be performed on that result.

results = ActiveRecord::Base.connection.execute('select * from purposes')

It returns PG::Result object :

#<PG::Result:0x007fd5a09686d8 status=PGRES_TUPLES_OK ntuples=3 nfields=5 cmd_tuples=3>

We can verify it simply using .class as we normally do in ruby.

results.class
=> PG::Result

This PG::result object contains fields like status, ntuples, cmd_tuples and each of it has its own meaning.

status: It describes query executed successfully or not.

ntuples: Returns the number of tuples in the query result.

nfields: Returns the number of columns in the query result.

cmd_tuples: Returns the number of tuples (rows) affected by the SQL command.

If we are using this first time to execute raw query and expect result directly and it gives result as pg object then question arises how we can retrieve actual data from it and what kind of operations can be performed on it. We will have a look at basic operations.

PG::result object is actually a collection of Hash and it contains data retrieved in string format(No matter what its data type of column as per ruby). No conversion is performed to convert the values to the appropriate Ruby type, other than NULL to nil.

If we want to perform some operation on each and every result record then we can simple iterate as it is collection of hash

results.each do |result|
   puts result
end

To get any particular record as per sequence. We can simply do.

results[ n ] → Hash

Returns tuple n as a hash. Max record we can access with it can be res[ ntuples - 1] as it is collection of hash.

To retrieve all column_names from result

results.fields

Returns array of column names.

To get array of result

results.to_a
 results.entries

Returns array of resultant data. This array contains of tuples as hash and each hash contains column name as key and its associated column_value as value.

To get all column values.

results.values

Returns nested array of values of each tuple.

To get column values of nth column.

results.column_values(n)

Returns array of values of nth column.

To get column values with particular column name.

results.field_values( column_name )

Returns array of values for particular column_name.

To get errors if operation fails.

results.error_message

To get total number of columns in result

 results.nfields

One of the important method regarding this is autoclear?. This checks result will be auto cleared from memory or not. If it returns false then it will not be auto cleared from memory and we need to clear this manually by using clear method. We can verify result cleared or not with help of method cleared?

803d79903d70796895388f80c91ad927
About Namrata Ukirde

I am ROR developer, Passionate about learning new things. Love to write poems.

1 Comment

  • 21c70a18e61e4e3a5752b187065a7bdc

    s 23-Aug-2017 · Reply

    s

Your comment

*

*