ActiveRecord query for joining has_and_belongs_to_many relation

Question

I have 3 Ruby on Rails tables: Users, Groups and Posts (plus a join table "groups_users" for the has_and_belongs_to_many).....

Here are the relevant table definitions:

  create_table "posts", force: true do |t|
    t.integer  "user_id"
    t.integer  "group_id"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  create_table "groups", force: true do |t|
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  create_table "users", force: true do |t|
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  create_table "groups_users", id: false, force: true do |t|
    t.integer "user_id",  null: false
    t.integer "group_id", null: false
  end

And the models:

Group Model:
    has_and_belongs_to_many :users, dependent: :destroy
    has_many :posts, inverse_of: :group, dependent: :destroy

User Model:
    has_many :posts, inverse_of: :user, dependent: :destroy
    has_and_belongs_to_many :groups, dependent: :destroy
    has_many :groupposts, through: :groups, source: :posts, class_name: "Post"

Post Model:
    belongs_to :group, inverse_of: :posts
    belongs_to :user, inverse_of: :posts

A Post's Group might be Null, so users can make general Posts without any Group (group_id == nil), or they can make Posts within one of the Groups they belong to.

I'm trying to figure out if there is a single Active Record Query that will give me: (1) all general Posts (no group_id) from any user, PLUS (2) all Posts to any Group that a User belongs to.

So something like:

Post.where(:group_id => nil)

uniquely joined with:

@user.groupposts

I think it's something in the neighborhood of:

Post.includes(:groups [:users]).where("(posts.group_id IS NULL) OR (posts.groups.users CONTAINS ?)", @user.id) # THIS IS NOT CORRECT, THE SECOND HALF OF THE 'OR' STATEMENT IS GARBAGE

Richard Richard · Accepted Answer · 2015-01-14T02:41:31

You cannot have that result in the same query ( you can have it if you write the complete query with an UNION) since your conditions are being applied for the same field, you should have 2 different queries for it.

For the General Post you can try with this

Post.where("group_id is null")
#or in scope
scope :general_posts, lambda { where ("group_id is null") }

All the posts from the user groups

Post.where( group_id: User.uniq.pluck(:id) )
#scope
scope :user_groups, lambda { where( group_id:  User.uniq.pluck(:id) ) }

Hope this could help you

ActiveRecord query for joining has_and_belongs_to_many relation

1 Answers