Rails SQL Where Count

Question

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

Are you using that query alone or combining with others queries? — Pavan, Jul 09 '14 at 14:42
Don't call `Post.all` if you are using Rails 3: It actually triggers the SQL query. Use `Post.scoped` to return the default scope without triggering the SQL query (if using Rails 4: `Post.where(nil)` http://stackoverflow.com/questions/18198963/with-rails-4-model-scoped-is-deprecated-but-model-all-cant-replace-it) — MrYoshiji, Jul 09 '14 at 15:38
I've changed `@posts = Post.all` to `@posts = Post.where(nil)` but i still get the same error `SQLite3::SQLException: near "*": syntax error: SELECT posts.* FROM "posts" INNER JOIN "sec_photos" ON "sec_photos"."post_id" = "posts"."id" GROUP BY posts.id HAVING COUNT(sec_photos.*) > 2` — PMP, Jul 09 '14 at 15:47

score 5 · Answer 1 · edited May 23 '17 at 10:26

5

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?

edited May 23 '17 at 10:26

Community

1
1

answered Jul 09 '14 at 14:45

MrYoshiji

54,334
13
124
117

This gives me this error 'SQLite3::SQLException: near "*": syntax error: SELECT "posts".* FROM "posts" INNER JOIN "sec_photos" ON "sec_photos"."post_id" = "posts"."id" GROUP BY posts.id HAVING COUNT(sec_photos.*) > 2' – PMP Jul 09 '14 at 15:12
I detailed my question a bit more – PMP Jul 09 '14 at 15:15
This error comes from the SELECT clause: `SELECT "posts".`: The query is not selecting anything at all, and I don't know why. You could force it to select everything by adding `.select('posts.*')` to your scope – MrYoshiji Jul 09 '14 at 15:37

Rails SQL Where Count

1 Answers1