How to understand the SQL behind an ActiveRecord Query

Quick example, to see and understand better how an ActiveRecord query using scopes or methods in the model works at the SQL level and how to run it, all in the same example

Ruby version

Let say you have a database with users and you have some scopes to filter by non-demo, active, and completed providers with a specific Role (CanCanCan behind the scenes) :

User.providers.real.active.completed

Let's look into the SQL version:

If we run this command

irb(main):007:0> User.providers.real.active.completed.to_sql

We would get this:

=> "SELECT \"users\".* FROM \"users\" INNER JOIN \"users_roles\" ON \"users_roles\".\"user_id\" = \"users\".\"id\" INNER JOIN \"roles\" ON \"roles\".\"id\" = \"users_roles\".\"role_id\" WHERE (roles.name = 'Provider') AND \"users\".\"demo_account\" = 'f' AND (\"users\".\"organization_id\" NOT IN (SELECT \"organizations\".\"id\" FROM \"organizations\" WHERE \"organizations\".\"demo_account\" = 't' ORDER BY \"organizations\".\"name\" ASC)) AND \"users\".\"is_active\" = 't' AND \"users\".\"signup_state\" = 'Completed'"

Then we just need to replace the invalid characters such as \ and translate that into a SQL version like this:

SUMMARY_PROVIDERS_REPORT_SQL = <<-SQL
WITH real_providers AS
  ( SELECT users.* FROM users
    INNER JOIN users_roles ON users_roles.user_id = users.id
    INNER JOIN roles ON roles.id = users_roles.role_id
    WHERE (roles.name = 'Provider') AND users.demo_account = 'f'
      AND (users.organization_id NOT IN (
        SELECT organizations.id FROM organizations WHERE organizations.demo_account = 't' ORDER BY organizations.name ASC)
      )
      AND users.is_active = 't' AND users.signup_state = 'Completed')
select real_providers.id, real_providers.name from real_providers
SQL

In order to run it in the Rails console for example:

report_results = ActiveRecord::Base.connection.execute(SUMMARY_PROVIDERS_REPORT_SQL)
report_results.entries

That will give you the id and name of all those valid providers