#8817 - missing table in FROM clause, scoped_search etc

Hi all,

I could do with some help on Bug #8817: PGError: ERROR: missing FROM-clause entry for table "hostgroups" - Foreman,
a regression issue with AR, scoped_search and our authorisation system.
The issue was introduced by me when I fixed
Bug #5841: Foreman dashboard slow in 1.5 for non admin users - Foreman, changing what was a query to
fetch reports with a list of all host IDs that a user had access to into
a subquery:

The problem now occurring is that when a user has access restricted by
host group, the SQL query being generated refers to the hostgroups
table, and when this is used in a subquery, the extra table isn't being
included. When it's used outside of the subquery, it works fine (e.g.
on hosts#index). I'd bet this is why the code was the way it was after
the auth system rewrite.

Here's a simple demonstration, using ss directly and skipping the
authorisation system:

2.0.0-p353 :001 > Report.where('host.id' =>
Host.search_for('hostgroup_id = 5'))
Report Load (0.1ms) SELECT "reports".* FROM "reports" WHERE
"host"."id" IN (SELECT "hosts"."id" FROM "hosts" WHERE "hosts"."type" IN
('Host::Managed') AND (("hostgroups"."id" = '5')) ORDER BY
"hosts"."name" ASC)
Hirb Error: SQLite3::SQLException: no such column: hostgroups.id: SELECT
"reports".* FROM "reports" WHERE "host"."id" IN (SELECT "hosts"."id"
FROM "hosts" WHERE "hosts"."type" IN ('Host::Managed') AND
(("hostgroups"."id" = '5')) ORDER BY "hosts"."name" ASC)

But evaluating Host.search_for by itself generates a correct SQL query,
including all of the extra tables.

I found a related issue in AR with #to_sql
(https://github.com/rails/rails/issues/6132) not including the extra
table, which indeed seems to work correctly in Rails 4.2.0, but the
subquery issue still seems to exist.

If you know how to solve it, please go ahead and post a PR, don't wait
for me.

Thanks!

ยทยทยท -- Dominic Cleal Red Hat Engineering