I rely on #to_sql to very quickly inspect the SQL of a relation object before executing it. One limitation of this however is that it’s only useful when the execution of the relation does not involve mutating the query further e.g. the query executed when calling #to_a / #records is the same as the one returned by #to_sql on the underlying relation.
Calculation methods like #sum, #count and #pluck, and finder methods like #first, #last and #find_by will modify the relation and execute the query, so there’s currently no easy way to inspect their SQL after the fact. Being able to do so can be useful to validate the expected query when constructing complex relations.
I’d like to propose adding #to_sql support for these methods so we can do things like:
> puts Comment.joins(:post).where(post: { title: "Better SQL inspection" }).to_sql.pluck(:id, :author)
#=> SELECT "comments"."id", "author" FROM "comments" INNER JOIN "posts" AS "post" ON "post"."id" = "comments"."post_id" WHERE "post"."title" = 'Better SQL inspection'
> puts Account.where(firm_id: 1).to_sql.sum(:credit_limit)
#=> SELECT SUM("accounts"."credit_limit") FROM "accounts" WHERE "accounts"."firm_id" = 1
> puts Post.where("title ILIKE ?", "%Rails%").to_sql.first
#=> SELECT "posts".* FROM "posts" WHERE (title ILIKE '%Rails%') ORDER BY "posts"."id" ASC LIMIT 1
> puts Post.all.to_sql.find_by(id: 1)
#=> SELECT "posts".* FROM "posts" WHERE "posts"."id" = 1 LIMIT 1
I’d be happy to send a pull request if there’s any interest in this.
I like this idea. For my use case I wanted to see the explain output for a sum query I was working on. However, I wanted to run the explain query against a production database. The app had PgHero available which made it easy to get the results of an explain query from production, but obviously only for SQL queries.
One downside of that #explain proxy is that it requires calling #inspect to trigger the query. This is fine for #explain as it’s mostly used in the console, which calls #inspect automatically, but I think it would be an annoying breaking change for #to_sql as people often use it to build their own SQL queries.
I wonder if the block approach would work well here:
Comment.joins(:post).merge(Post.published).to_sql do |relation|
relation.count
end
That way, we only change the behavior if block_given?
One downside of that #explain proxy is that it requires calling #inspect to trigger the query. This is fine for #explain as it’s mostly used in the console, which calls #inspect automatically, but I think it would be an annoying breaking change for #to_sql as people often use it to build their own SQL queries.
This is a good point, and I’m not keen to introduce a breaking change here either. I’ve already hacked on this and worked around it by delegating missing proxy methods to #to_s. The Rails suite relies on #to_sql quite heavily to assert expected queries and it’s passing on my branch.
I wonder if the block approach would work well here:
I didn’t want to propose the block approach to keep the API consistent with #explain, and possibly future similar implementations, but yes this would be a good alternative workaround to the above.