Prepared statements in Rails

Rails 3.1 and above has an awesome feature of prepared_statements. The basic idea behind prepared statements is to compile SQL statements once and cached for it future use. In other words - The benefit to prepared statements is that the database does not have to compile a query plan for every piece of SQL sent to it, potentially saving a lot of time.

Checkout an excellent blog(blog) to know more on prepared_statements.

Recently, I had to get raw sql from AR object so that I can pass it to EXPLAIN statement to get total number of rows (check this nice blog from Yogendra). As prepared_statements are enabled by default on PostgreSQL, I used to get following sql string:

 SELECT "contacts"."id" FROM "contacts" WHERE "contacts"."user_id" = $1 

In above query, the variable $1 was not replaced by actual id of user. On executing this query, I got exception:

ActiveRecord::StatementInvalid Exception: PG::UndefinedParameter: ERROR:  there is no parameter $1

For this, I had to set prepared_statements flag in database.yml to false. But this will decrease performance of overall apTo solve this, I used unprepared_statement { .. }.

 Contact.connection.unprepared_statement { @contacts.to_sql } 

This gave me:

 SELECT "contacts"."id" FROM "contacts" WHERE "contacts"."user_id" = "2" 

This will not work if you have a query like this:

 Contact.where("user_id IN (?)", current_user.account.users.select('id')) 

Here, current_user.account.users.select('id') is a subquery. We need to unprepare the subquery separately to make whole query work with to_sql.

subquery = Contact.connection.unprepared_statement { current_user.account.users.select('id').to_sql }
Contact.where("user_id IN (#{subquery})")