CODEWITHSUNDEEP
ruby-on-railsinclude
Rivero Felipe
Rivero Felipe

Reputation: 187

Queries with include in Rails

I have the following problem. I need to do a massive query of table named professionals but I need to optimize the query because for each professional I call the associated tables.

But I have a problem with two associated tables: comments and tariffs.

Comments:

I need to call 3 comments for each professional. I try with:

@professionals.includes(:comments).where(:comments => { type: 0 } ).last(3)

The problem the query only brings 3 professionals, not what I need, all the professionals with only three comments where type be equal to zero.

And when I try: 

@professionals.includes(:comments).where(:comments => { type: 0 } )

The result is only professionals with (all the) comments when I need all the professional with or without comments. But if the professional have comments I only need the last three comments where the type be equals zero

Tariffs:

With tariffs I have a similar problem, in this case I need the last 4 tariffs for each professional. I try with: 

@professionals.includes(:tariffs).last(4)

But only brings the last 4 professionals.

Models:

class Comment < ActiveRecord::Base
      belongs_to :client
      belongs_to :professional

end

class Professionals < ActiveRecord::Base
      has_many :comment

end

Upvotes: 1

Views: 58

Answers (1)

Mohamad
Mohamad

Reputation: 35349

You can't use limit on the joining table in ActiveRecord. The limit is applied to the first relation, which in this case happens to be @professionals.

You have a few choices choices:

  1. Preload all comments for each professional and limit them on output (reduces the number of queries needed but increases memory consumption since you are potentially preloading a lot of AR objects).
  2. Lazy load the required number of comments (increases the number of queries by n+1, but reduces the potential memory consumption).
  3. Write a custom query with raw SQL.

If you preload everything, then you don't have to change much. Just limit the number of comments white iterating through each @professional.

@professionals.each do |professional|
  @professional.comments.limit(3)
end

If you lazy load only what you need, then you would apply the limit scope to the comments relation.

@professionals.all
@professionals.each do |professional|
  @professional.comments.where(type: 0).limit(3)
end

Writing a custom query is a bit more complex. But you might find that it might be less performant depending on the number of joins you have to make, and how well indexed your table is.

I suggest you take approach two, and use query and fragment caching to improve performance. For example:

- cache @professionals do
  - @professionals.each do |professional|
    - cache professional do
      = professional.name

This approach will hit the database the first time, but after subsequent loads comments will be read from the cache, avoiding the DB hit. You can read more about caching in the Rails Guides.

Upvotes: 2

Related Questions