SQL output of ActiveRelation or Arel query that I would like

correlated_subquery

SELECT user_id, fb_request_id 
FROM   monitor_requests mr 
WHERE  current_state = '#{MonitorRequest::DENIED}' 
AND    fb_id IS NULL 
AND    fb_name IS NULL 
AND    created_at < '#{1.week.ago}' 
AND    user_id IS NOT NULL 
AND    fb_request_id IS NOT NULL 
AND    EXISTS (SELECT * FROM monitor_requests WHERE current_state = '#{MonitorRequest::PENDING}' AND fb_request_id = mr.fb_request_id)

Since you're referencing the main query (mr) in the exists, I don't know how to make it happen...Here's something you CAN do

suppliers= Supplier.arel_table
orders= Order.arel_table
suppliers_with_orders = Supplier.where(
                          Order.where(orders[:supplier_id]
                                        .eq(suppliers[:id])).exists).to_sql =>
"SELECT `suppliers`.* FROM `suppliers` 
 WHERE (EXISTS (SELECT `orders`.* 
                FROM `orders` 
                WHERE `suppliers`.`id` = `orders`.`supplier_id`))"

I would normally approach the problem as two queries, or go raw SQL.

I got it: - this appears to generate the correct query:

mr = MonitorRequest.arel_table
mr_alias = mr.alias("m_r")

sql = mr.project("user_id, fb_request_id").
from(mr_alias).
where(mr_alias[:current_state].eq(MonitorRequest::DENIED)).
where(mr_alias[:fb_id].eq(nil)).
where(mr_alias[:fb_name].eq(nil)).
where(mr_alias[:created_at].lt(1.week.ago)).
where(mr_alias[:user_id].not_eq(nil)).
where(mr_alias[:fb_request_id].not_eq(nil)).
where(MonitorRequest.where(mr[:current_state].eq(MonitorRequest::PENDING)).
where(mr[:fb_request_id].eq(mr_alias[:fb_request_id])).exists).to_sql

generates:

SELECT user_id, fb_request_id
FROM "monitor_requests" "m_r"
WHERE "m_r"."current_state" = 'denied'
AND "m_r"."fb_id" IS NULL
AND "m_r"."fb_name" IS NULL
AND "m_r"."created_at" < '2011-12-20 20:25:48.538148'
AND "m_r"."user_id" IS NOT NULL
AND "m_r"."fb_request_id" IS NOT NULL
AND EXISTS (SELECT "monitor_requests".* FROM "monitor_requests" WHERE "monitor_requests"."current_state" = 'pending' AND "monitor_requests"."fb_request_id" = "m_r"."fb_request_id")

Which is more readable - the raw SQL or Arel? Not sure.

The Arel is unfortunately needlessly complex given that the outer query conditions don't need to use the alias in the raw SQL.
The alias is really only needed on the condition in the correlated subquery.
But you have to use mr_alias in the outer query or you get this Postgres error - 'PGError: ERROR: invalid reference to FROM-clause entry for table "monitor_requests"' Lame.