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
Difference Between Union and Union All
Which Is Faster/Best? Select * or Select Column1, Colum2, Column3, etc
Using an Alias Column in the Where Clause in Postgresql
How to Do a Batch Insert in MySQL
Strange Duplicate Behavior from Group_Concat of Two Left Joins of Group_Bys
Is There Any Function in Oracle Similar to Group_Concat in MySQL
MySQL Trigger to Prevent Insert Under Certain Conditions
How to Use Script Variables in Psql
How to Concatenate Multiple MySQL Rows into One Field
Identity Increment Is Jumping in SQL Server Database
Sqlite - Upsert *Not* Insert or Replace
Simple Way to Calculate Median With MySQL
Index For Finding an Element in a Json Array
Find All Tables Containing Column With Specified Name - Ms SQL Server