16

Say I have a Sequel expression like:

db.select(:id).from(:some_table).where(:foo => 5)

Is there a way to get the SQL string that this will generate (i.e. "SELECT id FROM some_table WHERE foo = 5")? I notice that calling inspect or to_s on the result of the above expression includes that generated SQL, but not sure how to access it directly.

And how about Sequel expressions that do not return a dataset, like:

db.from(:some_table).update(:foo => 5)

Is it possible to see the SQL from this before it's executed?

the Tin Man
  • 150,910
  • 39
  • 198
  • 279
Matt Zukowski
  • 4,080
  • 4
  • 35
  • 38

1 Answers1

31

You can call sql on dataset: 

db.select(:id).from(:some_table).where(:foo => 5).sql # => "SELECT `id` FROM `some_table` WHERE (`foo` = 5)"

For update queries you can do this:

db.from(:some_table).update_sql(:foo => 5) # => "UPDATE `some_table` SET `foo` = 5"

Some similar useful methods:

insert_sql
delete_sql
truncate_sql
mechanicalfish
  • 11,846
  • 3
  • 41
  • 40
  • What is `db` in this case and how do you access/instantiate it? – Peter Alfvin Dec 16 '13 at 19:26
  • Is there anyway to get at this from an ActiveRecord Relation? – Peter Alfvin Dec 16 '13 at 19:26
  • It's a Sequel connnection like `db = Sequel.connect('sqlite://blog.db')`. In `ActiveRecord::Relation`, you can just call `to_sql` – mechanicalfish Dec 16 '13 at 19:29
  • Thanks. :-) In the case of a particular Rails expression requiring multiple SQL statements to execute, do you know if `.to_sql` just gives the latest? – Peter Alfvin Dec 16 '13 at 19:39
  • Not sure what you mean. It will show you the statement related to the relation you call it on (it will show joins etc but not includes and preloads). – mechanicalfish Dec 16 '13 at 19:45
  • I'm not sure I know what I mean either, perhaps because I don't know what an include or preload is in this context. ;-) Is it sometimes necessary for Rails to execute a query in order to even construct a relation? I'm trying to understand the paragraphs starting with "Please note" at http://weblog.rubyonrails.org/2011/12/6/what-s-new-in-edge-rails-explain/ – Peter Alfvin Dec 16 '13 at 19:50
  • Is there a known way to do this for distinct? For example `db[:table].distinct(:id).sql` and `db[:table].distinct_sql(:id)` don't work. – abhillman Sep 29 '16 at 22:42