Difficult Rails ActiveModel Query

Question

I am building a Rails app which has a User model, joined to a Interests model through a has_many :through relationship using a UserInterests join model.

Given a set of interests, what I want to do is find all users who have selected at least one interest in that set, OR who have not selected any interests at all. I am thinking of something a bit like this:

users = users.joins(:user_interests).where(["COUNT(user_interests) = 0 OR user_interests.interest_id IN ?", interest_ids])

This raises a Mysql syntax error. Any ideas how to achieve this?

Many thanks

Mysql::Error: Invalid use of group function: SELECT `users`.* FROM `users` INNER JOIN `user_interests` ON `user_interests`.`user_id` = `users`.`id` WHERE (COUNT(user_interests) = 0 OR user_interests.interest_id IN (1)) — AdamP, May 01 '15 at 07:43

score 0 · Answer 1 · answered May 01 '15 at 07:51

0

try this one,

users = User.joins(:user_interests).where("COUNT(user_interests) = 0 OR interest_id IN (?)", interest_ids)

answered May 01 '15 at 07:51

rick

1,675
3
15
28

Same error I'm afraid - I think the problem is in the use of the COUNT function rather than in the Rails syntax. My SQL knowledge is not good enough unfortunately! – AdamP May 01 '15 at 08:04
@AdamP What you have in user_interests? What is user_interests? column_name or array? – rick May 01 '15 at 08:07
In Rails, UserInterest is a model connecting Users and Interests using a has_many :through relationship. The user_interests database table has attributes of user_id and interest_id. So I want to find users for whom there is a user_interests entry with interest_id in my list of interest IDs, OR users who have no user_interests associated with them at all. – AdamP May 01 '15 at 08:11
@AdamP You can not count on function in a where clause. You should use having clause. Refer this http://stackoverflow.com/questions/4629744/activerecord-syntax-for-finding-all-items-with-an-average-rating-of-x-or-greater – rick May 01 '15 at 08:27
Thanks for that. It is certainly closer to what I need. I tried this: User.joins(:user_interests).group("user_interests.user_id").having("COUNT(user_interests.id) = 0"). There are 2 problems though. Firstly it will not return any users for which the user_interests group is empty (i.e. all of them!). Secondly I need to somehow combine it with a where clause - the idea is to return all users with no user_interests AND all users who have user_interests in a given set. – AdamP May 01 '15 at 13:21

score 0 · Answer 2 · answered May 01 '15 at 12:30

0

class User < ActiveRecord::Base
  scope :with_interests, ->(*interests) {
    joins(:user_interests).where(user_interests: {interest_id: interests.flatten.compact.uniq})
  }

  scope :no_interests, -> {
    where("not exists (select * from user_interests ui2 where ui2.user_id = users.id)")
  }
end

interest = Interest.first

User.with_interests(interest) + User.no_interests

answered May 01 '15 at 12:30

ilan berci

3,883
1
16
21

Thanks, but I need to use a single query as I plan to chain it with other query conditions – AdamP May 01 '15 at 14:24

score 0 · Answer 3 · answered May 01 '15 at 14:26

This worked for me - although it is not 100% ideal.

Add a user_interests_count field to users with a counter_cache
Search using a LEFT JOIN query:

users = User.joins("LEFT JOIN user_interests ON user_interests.user_id = users.id").where("(users.user_interests_count = ?) OR user_interests.interest_id IN (?)", 0, interest_ids)

Difficult Rails ActiveModel Query

3 Answers3