Rails 3 Query on Condition of an Association'S Count

Rails 3 query on condition of an association's count

The documentation on this stuff is fairly sparse at this point. I'd look into using Metawhere if you'll be doing any more queries that are similar to this. Using Metawhere, you can do this (or something similar, not sure if the syntax is exactly correct):

Customer.includes(:purchases).where(:purchases => {:count.gte => 2})

The beauty of this is that MetaWhere still uses ActiveRecord and arel to perform the query, so it works with the 'new' rails 3 way of doing queries.

Additionally, you probably don't want to call .all on the end as this will cause the query to ping the database. Instead, you want to use lazy loading and not hit the db until you actually require the data (in the view, or some other method that is processing actual data.)

Rails 3.2 way to count conditional associations without 1+n queries

I suggest to inner join the posts and let the database count with the help of group by. Then you don't need to instantiate the posts. The SQL should then look like:

SELECT users.*, count(posts.id) AS number_posts 
FROM users
LEFT OUTER JOIN posts
ON posts.user_id = users.id
AND posts.created_at > '2016-02-14 08:31:29'
GROUP BY users.id

Furthermore you can and take advantage of that select, which adds the counted posts dynamically as an additional attribute. You only can achieve the extended JOIN condition by using AREL.
You should push that into a named scope, like:

User < ActiveRecord::Base
has_many :posts
scope :with_counted_posts(time=1.day.ago) -> {
post_table = Post.arel_table
join = Arel::Nodes::On.new(Arel::Nodes::Equality
.new(post_table[:user_id], self.arel_table[:id])
.and(Arel::Nodes::GreaterThan.new(post_table[:created_at], time))
)
joins(Arel::Nodes::OuterJoin.new(post_table, join))
.group('users.id')
.select('users.*, count(posts.id) AS number_posts')
}
end

Of course there is potential for optimizations and extractions, but for some understanding reasons, I did it more extensive.
Then in the controller:

@users = User.with_counted_posts.order(:name)

the users/index.html.erb view could look like:

<table>
<tr>
<th>Name</th>
<th>Recent posts</th>
</tr>
<% @users.each do |user| %>
<tr>
<td><%= user.name %></td>
<td><%= user.number_posts %></td>
</tr>
<% end %>
</table>

Although I highly recommend to take advantage of the render :collection approach. The users/index.html.erb once again:

<table>
<tr>
<th>Name</th>
<th>Recent posts</th>
</tr>
<%= render @users %>
</table>

and the users/_user.html.erb partial:

  <tr>
<td><%= user.name %></td>
<td><%= user.number_posts %></td>
</tr>

I also wrote a blog post about the N+1 problem and ARel

Find all records which have a count of an association greater than zero

joins uses an inner join by default so using Project.joins(:vacancies) will in effect only return projects that have an associated vacancy.

UPDATE:

As pointed out by @mackskatz in the comment, without a group clause, the code above will return duplicate projects for projects with more than one vacancies. To remove the duplicates, use

Project.joins(:vacancies).group('projects.id')

UPDATE:

As pointed out by @Tolsee, you can also use distinct.

Project.joins(:vacancies).distinct

As an example

[10] pry(main)> Comment.distinct.pluck :article_id
=> [43, 34, 45, 55, 17, 19, 1, 3, 4, 18, 44, 5, 13, 22, 16, 6, 53]
[11] pry(main)> _.size
=> 17
[12] pry(main)> Article.joins(:comments).size
=> 45
[13] pry(main)> Article.joins(:comments).distinct.size
=> 17
[14] pry(main)> Article.joins(:comments).distinct.to_sql
=> "SELECT DISTINCT \"articles\".* FROM \"articles\" INNER JOIN \"comments\" ON \"comments\".\"article_id\" = \"articles\".\"id\""

Use Rails .where to return objects with an association count greater than 0

Somethling like:

Order.joins(:newspaper_placements).where(client_companies_media_company_id: all_cc_mc)

Getting associated objects count with condition in Rails

Merchant.joins(:offers, :gift_cards)
.select('merchants.id, COUNT(gift_cards) as offer_count,
COUNT(gift_cards) as gift_card_count')
.where(offers: { id: eligible_offer_ids },
gift_cards: { id: eligible_gift_cards_ids })
.group(:id)

This will return an ActiveRecord_Relation of the merchant's ids, offer_counts, and gift_card_counts where eligible offers and gift card ids are in those arrays. You can then iterate through for whatever you need :D

Rails activerecord count number of associations

Does your impressions tables' created_at column have index?

If you are querying it and there is none - you could add it by generating a migration file.

add_index(:impressions, :created_at)

And you could use pure SQL to add condition to your query like:

cards = Card.joins(:impressions)
.where("impressions.created_at > ? AND impressions.created_at < ?", Date.today-30.days, Date.today)
.group('cards.id')
.having('COUNT(impressions.*) >= 10')

Rails condition where association has_many has count more than one

def self.available
joins(:deals).where(:available_for_purchase => true).uniq
end

Joining the deals association will remove Packages without any deals ... SQL is doing the hard work here.

Then use :

Package.available


Related Topics



Leave a reply



Submit