New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Join to searchable model with nested associations not including tables #125

Open
domcleal opened this Issue Mar 17, 2015 · 2 comments

Comments

Projects
None yet
2 participants
@domcleal
Collaborator

domcleal commented Mar 17, 2015

In Foreman, we hit an issue in the following scenario:

  • host has many reports, reports belong to a host
  • hostgroup has_many hosts, hosts belong to a hostgroup
  • host has search definition: scoped_search :in => :hostgroup, :on => :name, :complete_value => true, :rename => :hostgroup

We then want to list reports for hosts that match a certain hostgroup, so:

Report.where(:reports => {:host_id => Host.search_for('hostgroup = One') })

This is equivalent to:

Report.where(:reports => {:host_id => Host.joins('hostgroup').where('hostgroups.name = "Foo"') })

But the resulting joined SQL query doesn't include the hostgroups table, so throws an error:

ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: hostgroups.name: SELECT "reports".* FROM "reports" WHERE "reports"."host_id" IN (SELECT "hosts"."id" FROM "hosts" WHERE (("hostgroups"."name" = 'One')))

It seems the right way to do this in Rails is:

Report.joins('host' => ['hostgroup']).where('hostgroups.name = "One"')

(http://guides.rubyonrails.org/active_record_querying.html#eager-loading-multiple-associations)

I've sort of managed to work around it by calling ScopedSearch::QueryBuilder.build_query directly and then generating an AR query with the right sequence as shown above, but it would be good to have some help building join queries in scoped_search itself - if this is the case, and I'm not missing something blindingly obvious.

In our case the scoped search queries could be querying a variety of objects associated to host depending on user configuration, so hardcoding this isn't an option. As a workaround, it's possible to make an inefficient query with IDs, but this was very slow with large numbers of hosts.

Here's a small application and unit test demonstrating it: https://github.com/domcleal/ss_join_search_issue (scoped_search 3.2.0, Rails 4.2.0, sqlite 3.8.8, Ruby 2.0.0).

@tbrisker

This comment has been minimized.

Show comment
Hide comment
@tbrisker

tbrisker Sep 27, 2016

Contributor

I think this is caused by the Rails 4 changes to the .includes method behaviour. The :includes values generated by the query builder should be :eager_loaded or :joinsed instead.

Contributor

tbrisker commented Sep 27, 2016

I think this is caused by the Rails 4 changes to the .includes method behaviour. The :includes values generated by the query builder should be :eager_loaded or :joinsed instead.

@tbrisker

This comment has been minimized.

Show comment
Hide comment
@tbrisker

tbrisker Sep 28, 2016

Contributor

Diving even deeper into this, looks like the issue may actually be in Rails ignoring .includes(:relation).references(:relation) inside an inner query for a where clause. However, I wonder why a LEFT JOIN is generated in this case instead of an INNER JOIN - since if we are searching on attributes of a related table, it makes no sense including rows which have no relation to it.

Contributor

tbrisker commented Sep 28, 2016

Diving even deeper into this, looks like the issue may actually be in Rails ignoring .includes(:relation).references(:relation) inside an inner query for a where clause. However, I wonder why a LEFT JOIN is generated in this case instead of an INNER JOIN - since if we are searching on attributes of a related table, it makes no sense including rows which have no relation to it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment