CODEWITHSUNDEEP
sqlruby-on-railssqlitecount
PMP
PMP

Reputation: 231

Rails SQL Where Count

I have a Posts Table and A Sec_photo table :

class Post < ActiveRecord::Base
  has_many :sec_photos

I am trying to do an advanced search form where it finds posts based on their sum of sec_photos : @posts = @posts.where(:sec_photos.count > 2) is failing and I have looked online and attempted many other solutions but none seem to work.

Does anyone have any ideas?

Ps: It's a necessity for the line to be in the form @posts = @posts.where as that coincides with other conditions.

The advanced search from searches for other fields like category_id and location_id as such 

@posts = Post.all
     @posts = @posts.where('category_id = ?', @advanced_search.category_search) if @advanced_search.category_search.present?
     @posts = @posts.where('location_id = ?', @advanced_search.location_search) if @advanced_search.location_search.present?
     @posts = @posts.where("strftime('%m %Y', created_at) = ?", @advanced_search.newdate_search.strftime('%m %Y')) if @advanced_search.newdate_search.present?

The last option would be to show posts with sec_photos either more than 2 or less than 2

Upvotes: 2

Views: 4690

Answers (1)

MrYoshiji
MrYoshiji

Reputation: 54882

You can do as following:

@posts = Post.whatever_scopes_you_use
@posts = @posts.joins(:sec_photos).group('posts.id').having('COUNT(sec_photos.id)
> 2')

This last line will select all posts having strictly more than 2 sec_photos associated. Of course, you can easily make a scope from this, accepting a count variable to make it dynamic ;-)

From my previous answer: How to return results filtered on relations count to a view in RAILS?

Upvotes: 5

Related Questions