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: It returns the total number of tuples which we get 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). It does not perform any conversion 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?