nested subqueries missing the subqueries entirely in rails

[roscom]

The following sql is valid and works:

SELECT roles.*
     FROM roles
     WHERE roles.id in
         (SELECT role_id
          FROM domains
          WHERE domains.expired_on IS NULL
            AND domains.id in
              (SELECT domain_id
               FROM domain_pages
               WHERE domain_pages.expired_on IS NULL
                 AND domain_pages.id in
                   (SELECT domain_page_id
                    FROM page_keywords
                    WHERE page_keywords.expired_on IS NULL
                      AND page_keywords.active IS TRUE)))
       AND roles.role_type_id = 2
       AND roles.expired_on IS NULL;

and generates the following arel equivalent in scuttle:

Role.select(Role.arel_table[Arel.star]).where(
  Role.arel_table[:id].in(
    Domain.select(:role_id).where(
      Domain.arel_table[:expired_on].eq(nil).and(
        Domain.arel_table[:id].in(
          DomainPage.select(:domain_id).where(
            DomainPage.arel_table[:expired_on].eq(nil).and(
              DomainPage.arel_table[:id].in(
                PageKeyword.select(:domain_page_id).where(
                  PageKeyword.arel_table[:expired_on].eq(nil).and(PageKeyword.arel_table[:active])
                )
              )
            )
          )
        )
      )
    )
  ).and(
    Role.arel_table[:role_type_id].eq(2).and(Role.arel_table[:expired_on].eq(nil))
  )
)

which results in the following nonsense sql when run through the rails console:

Role Load (0.3ms)  SELECT `roles`.* FROM `roles` WHERE 1=0 AND `roles`.`role_type_id` = 2 AND `roles`.`expired_on` IS NULL LIMIT 11

I can see this is a rails issue, not scutlle.io.

Reading your comments on nested subqueries, "it is a matter of the right 'incantation'" which I've been experimenting with to no avail.

Would you be able to provide some direction on how to incant this the right way? "#project" does not seem to be useful in this context.

Regards Ross

[camertron]

Hey @roscom,

If I replace all the .select calls with .arel_table.project calls, it appears to work, eg:

Role.select(Role.arel_table[Arel.star]).where(
  Role.arel_table[:id].in(
    Domain.arel_table.project(:role_id).where(
      Domain.arel_table[:expired_on].eq(nil).and(
        Domain.arel_table[:id].in(
          DomainPage.arel_table.project(:domain_id).where(
            DomainPage.arel_table[:expired_on].eq(nil).and(
              DomainPage.arel_table[:id].in(
                PageKeyword.arel_table.project(:domain_page_id).where(
                  PageKeyword.arel_table[:expired_on].eq(nil).and(PageKeyword.arel_table[:active])
                )
              )
            )
          )
        )
      )
    )
  ).and(
    Role.arel_table[:role_type_id].eq(2).and(Role.arel_table[:expired_on].eq(nil))
  )
)

Well, actually it almost works. Unfortunately it looks like Scuttle doesn't know how to handle IS TRUE, so you'll have to modify PageKeyword.arel_table[:active] so it's PageKeyword.arel_table[:active].eq(true).

That said, have you considered using joins for this instead of all these nested subqueries? Eg:

SELECT roles.*
FROM roles
JOIN domains
    ON domains.role_id = roles.id
    AND domains.expired_on IS NULL
JOIN domain_pages
    ON domain_pages.domain_id = domains.id
    AND domain_pages.expired_on IS NULL
JOIN page_keywords
    ON page_keywords.domain_page_id = domain_pages.id
    AND page_keywords.expired_on IS NULL
    AND page_keywords.active IS TRUE
WHERE roles.role_type_id = 2
AND roles.expired_on IS NULL

which becomes

Role.select(Role.arel_table[Arel.star]).where(
  Role.arel_table[:role_type_id].eq(2).and(Role.arel_table[:expired_on].eq(nil))
).joins(
  Role.arel_table.join(Domain.arel_table).on(
    Domain.arel_table[:role_id].eq(Role.arel_table[:id]).and(Domain.arel_table[:expired_on].eq(nil))
  ).join_sources
).joins(
  Role.arel_table.join(DomainPage.arel_table).on(
    DomainPage.arel_table[:domain_id].eq(Domain.arel_table[:id]).and(DomainPage.arel_table[:expired_on].eq(nil))
  ).join_sources
).joins(
  Role.arel_table.join(PageKeyword.arel_table).on(
    PageKeyword.arel_table[:domain_page_id].eq(DomainPage.arel_table[:id]).and(
      PageKeyword.arel_table[:expired_on].eq(nil).and(PageKeyword.arel_table[:active])
    )
  ).join_sources
)