CODEWITHSUNDEEP
mysqlruby-on-rails
nullnullnull
nullnullnull

Reputation: 8189

Rails: Order found objects with a has_and_belongs_to_many relationship

Class User

has_many_belongs_to_many :books

end

Class Book

has_many_belongs_to_many :users

end

I'm trying to create a list of books and order them by the number of users they have:

Book.find(:all,:conditions => ['title LIKE ?', "%#{params[:autocomplete]}%"],
:limit => 5, :include => :users, :order => "users.count DESC")

Unfortunately, this throws an error telling me that there is no users column:

<pre>Mysql::Error: Unknown column 'users.count' in 'order clause': SELECT  `books`.*
FROM `books`  WHERE (title LIKE '%pop%') ORDER BY users DESC LIMIT 5</pre>

If I remove "count," I still get an error saying that it Mysql can't find "users." Any idea how to get this data?

Upvotes: 0

Views: 242

Answers (1)

gabrielhilal
gabrielhilal

Reputation: 10769

When you call users.count DESC, rails will try to find the column users.count. You need to remove the count and refer to the user.id instead.

Try to change the :order => "users.count DESC" for :order => "COUNT(users.id) DESC".

I cannot test it now, but I believe it should solve the problem...

EDIT - Try the new way to query (Rails 3). Should be something like the below:

Book.where('title LIKE ?', "%#{params[:autocomplete]}%").includes(:users).order(COUNT(users.id) DESC").limit(5)

Upvotes: 1

Related Questions