Can you join another table that isn't an association in ARel?

Question

I've got some SQL that's working when I want to look for particular pages that have the particular schemes assigned to it (all through the scheme_assignment):

Page.find_by_sql("
    SELECT pages.id
    FROM pages
    INNER JOIN scheme_assignments ON (scheme_assignments.schemable_type = 'Page' AND
                                      scheme_assignments.schemable_id = pages.sid)
    INNER JOIN schemes ON (schemes.sid = scheme_assignments.scheme_id AND
                           schemes.sid IN (4,6,7))
    GROUP BY pages.id")

When I tried to convert this to ARel:

Page.select("pages.id")
    .joins("scheme_assignments")
    .on("scheme_assignments.schemable_type = 'Page' AND scheme_assignments.schemable_id = pages.sid")
    .joins("schemes")
    .on("schemes.sid = scheme_assignments.scheme_id AND schemes.sid IN (1)")
    .group("pages.id")

I would get this error: NoMethodError: undefined methodjoin_sql' for "schemes":String`

How come? Is it because it's not an association in my Page model? Could one go about converting that SQL to ARel without creating the associations in the model?

Answer 1

Page.joins("INNER JOIN scheme_assignments 
        ON (scheme_assignments.schemable_type = 'Page' AND scheme_assignments.schemable_id = pages.sid) 
        INNER JOIN schemes 
        ON (schemes.sid = scheme_assignments.scheme_id AND schemes.sid IN (4,6,7))").
    select("DISTINCT pages.*");

Answer 2

Rails Guides

When you have an association set up, Rails knows what kind of join to use. I think (haven't tried it though) it should work this way:

Page.select("pages.id").joins("INNER JOIN scheme_assignments ON (scheme_assignments.schemable_type = 'Page' AND scheme_assignments.schemable_id = pages.sid)")...