CODEWITHSUNDEEP
mysqlsqlruby-on-rails-3activerecord
David
David

Reputation: 7303

Help with HABTM query (Rails 3)

I have the following models:

#user.rb
class User < ActiveRecord::Base
  has_and_belongs_to_many :groups
end

#group.rb
class Group < ActiveRecord::Base
  has_and_belongs_to_many :users
end

#join table migration
class CreateGroupUserJoinTable < ActiveRecord::Migration
  def self.up
    create_table :groups_users, :id => false do |t|
      t.integer :user_id
      t.integer :group_id
    end
  end

  def self.down
    drop_table :groups_users
  end
end

I need to make the following query:

@group = Group.find(:all, :include => users, :conditions => ["users count < ?", group_size]).first

But this gives me the following error:

SQLite3::SQLException: near "count": syntax error: SELECT "groups".* FROM "groups" WHERE (users count < 2) LIMIT 1

I've also tried this:

@group = Group.where("users count < ?", group_size).first

But I get the same error. What am I doing wrong?

Upvotes: 0

Views: 793

Answers (2)

Fran&#231;ois Beausoleil
Fran&#231;ois Beausoleil

Reputation: 16515

users.count is what you need - notice you left a space. You're getting a syntax error, not some strange error.

@group = Group.find(:all,
                    :include => users,
                    :conditions => ["users.count < ?", group_size]).first

Now, if you only really wanted the first entry, you should really do:

@group = Group.first(:include => users,
                     :conditions => ["users.count < ?", group_size])

Notice I'm calling #first, not #find. #find will be deprecated shortly - use #all, and #first instead.

Upvotes: 0

nzifnab
nzifnab

Reputation: 16110

Group.select("groups.*, count(users.id) AS user_count").joins(:users).group("groups.id").having(["count(users.id) < ?", group_size])

I think that'll probably do it for you...

Upvotes: 1

Related Questions