Hello. Is it possible to get raw sql query result from ActiveRecord::Relation ?
Now I can make it this way:
> ```
> sql = Note.select('count(*), commit_id').group(:commit_id).to_sql
> ```
> ActiveRecord::Base.connection.execute(sql).to_a
I would like something like this:
Note.select('count(*), commit_id').group(:commit_id).raw
But didn't find such method.+1, I do this a lot.
+1, too.
I was planning to add this method because calling to_sql is not a good solution. I think the name we choose was select_all.
+1 from me as well. This would be a great addition to the current API.
Cheers,
— Yves Senn
Should that mimick the low-level API? select_value, select_rows, etc?
On the other hand I feel Arel and #to_sql are out of hand. In the beginning this was considered to be private to AR (this was certainly the point of view of Pratik, who implemented the initial integration IIRC). AR could change completely Arel with something else in theory as long as the public interface was respected.
But Arel and #to_sql seem to be used widely :(.
How about #to_a and #to_h instead?
#to_h would return the results as a hash, useful when you are retrieving a single row, but many columns.
#to_a would return the results as an array of hashes, useful when you have multiple rows.
to_a is already used to return an Array of objects.
select_rows seems good to me.
But Arel and #to_sql seem to be used widely.
Yes, this is sad. They was never public API. But I think we can’t do anything besides make clear what is public and what is private API.
We should keep #to_sql and Arel as private API. The use-case to get the raw response from the driver remains.
It would be nice to use ActiveRecord to build the SQL but get access to the raw driver result and not the mapped objects.
Do the lower level methods like select_all, select_values, etc. accept Relations?
The current documentation is a bit confusing about the API (see http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/DatabaseStatements.html#method-i-select_values):
The method is in the docs, so it’s assumed to be public.
We name the argument arel, which let’s you in belief that arel is public as well
The example uses raw SQL.
It would be good to get some light into the dark.
#to_sql + #execute is widely used, because it’s a legitimate, and popular use case. Sometimes, we just need pure ruby objects. AR models become an overhead in those situations.
I was discussing with Aaron and we believe that passing relations to these methods should not be advised. This is why we want to add a method to relation to get the low level values.
so… select_one and select_all, maybe?
#to_sql + #execute is widely used, because it's a legitimate, and popular
use case. Sometimes, we just need pure ruby objects. AR models become an overhead in those situations.
AR::Base#connection and its API are totally public, but #to_sql wasn't intended to be. AR is not a SQL builder, it is a layer between Ruby and databases.
That's why guides have never covered Arel etc., those are tools used by AR to build SQL *internally*, not to be exposed to end-users.
I'd bet #to_sql ended up in api.rubyonrails.org because nobody nodoc'ed it, I doubt it was intentional.
I know it’s been a long time from the last reply. But I really want some method return raw results in ActiveRecord.
Actually I use ActiveRecord as a sql builder for some complex query. And it’s useful in most cases.
I added a patch for my project to do the following query:
PayRecord.group(:settlement_id, :pm).having('count() > 1’).select("count(), id, settlement_id, pm").select_all
Here’s the patch
class ActiveRecord::Relation
def select_all
@klass.connection.select_all(self.to_sql)
end
end
@Rafael Mendonça França What’s the schedule of this feature?
I’d like a way to work with raw data as it was returned by a SQL query. Mostly because Rails’ eager loading has a penchant for not working with ActiveRecord::QueryMethods.select, losing useful data in the process.
As for Arel and #to_sql… Why wasn’t it made public? Consider that ActiveRecord doesn’t even have a convenient interface for writing greater than/less than queries. Arel could be an answer to this problem, and more.
I took a stab at it: Add Relation#select_rows to retrieve raw results by marcotc · Pull Request #33839 · rails/rails · GitHub
Going through the review process soon.