gistfile1.sql

def for_user_with_role_sql(id, name)
      sql = <<-SQL
        WITH RECURSIVE organization_tree AS
        ( SELECT o.*
        FROM    organizations AS o
                JOIN roles    AS r
                ON      r.manageable_id   = o.id
                AND     r.user_id         = #{id}
                AND     r.manageable_type = 'Organization'
                AND     r.name            = '#{name}'

        UNION ALL

        SELECT o.*
        FROM   organizations AS o
               JOIN organization_tree
               ON     (
                             o.parent_id = organization_tree.id
                      )
        )
        SELECT * FROM   organization_tree
      SQL
    end