CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
Obromios
Obromios

Reputation: 16403

How to create a single query across associations

I have a user model that has one profile and also has one goal. The profile has a privacy field which is of type hstore, and in that field there can be a hash that indicates that it is OK to publicly display the goal, the hash looks like { 'show_goals' => '1' } if the goal can be displayed. I want a list of all goals that can be displayed publicly. My initial attempt was 

   def list_public
      profiles = Profile.includes(:user).where("privacy @> hstore('show_goals','1')")
      goals = []
      profiles.each do |p|
        goals << p.user.goals.first
      end
      goals
    end
  end

This works fine when there was a small number of users opting into allow their goals to be displayed, but is clearly not scaleable. Is there a single or a couple of ActiveRecord sql queries that can do the job of this code? I am using ruby on rails 5.1.

Upvotes: 1

Views: 31

Answers (1)

Stefan Daschek
Stefan Daschek

Reputation: 376

The easiest way is to also eager load the goals in your query:

profiles = Profile.includes(:user, user: :goals).where("privacy @> hstore('show_goals','1')")

This will produce one additional query fetching all the goals. You can then still use the remaining code as is, p.user.goals.first will not generate an additional database query.

Upvotes: 1

Related Questions