Correct design: a Post can have one User (author) plus many Users (tagged users)

I have a situation where I'm getting a bit stuck.

A Post can have a User (as an author), and a Post can also have many Users (as a post can have other users tagged in it).

In my Post model:

belongs_to :user
has_and_belongs_to_many :users

In my User model:

has_and_belongs_to_many :posts

It works fine generally, but is getting rather stuck when I want to include users on my posts query, and sort the posts by the author's name.

What should the correct design be? Perhaps replace user_id in P开发者_运维技巧ost with author_id?

---

I can imagine User and Users messing up your joins. But mind you, as far as my experience this is something that rails does not solve well. Just renaming it author (whilst improving the readability/expressing the intention better) will not help, since it will still point to the same table users.

When doing multiple joins with rails, it is very hard to express your conditions correctly. Rails creates aliases for your tables on the fly, unfortunately I have not find a way to address these aliases in my conditions.

So I think you will either have to resort to a handcrafted sql (which will be fast) or use rails, and get the posts sorted on author-name first, and then retrieve the list of tagged users per post second (which will be slower, but pure ruby).

I would go for the second option first, and optimize to the first if needed.

[UPDATE: add a scope definition]

Personally, I do not favor default_scope and instead would propose to define an explicit scope

scope :with_authors_sorted, lambda { 
  Post.includes(:type).includes(:user).
       order('year DESC, week DESC, users.display_name ASC, posts.created_at DESC') 
}

which you can then just use in your controller.

@posts = Post.with_authors_sorted

Hope this helps.