CODEWITHSUNDEEP
sqlruby-on-railsrubyruby-on-rails-5
Designer
Designer

Reputation: 1111

Listing users who have posts with Ruby on Rails 5

Currently I have this query to pull all users who have an avatar:

@users = User.all.order('RANDOM()').where.not(avatar: nil)

However I also need to check the users who have posts. How can I do that since the posts are in a different table?

Thank you.

Upvotes: 1

Views: 306

Answers (2)

Zoran
Zoran

Reputation: 4226

One way to get users with both an avatar and posts is to use SQL's EXISTS operator. We can then leverage ActiveRecord's scopes to make these queries reusable:

# app/models/user.rb
class User < ApplicationRecord
  scope :with_avatar, -> { where.not(avatar: nil) }
  scope :with_posts, -> { where('EXISTS(SELECT 1 FROM posts WHERE user_id = users.id)') }
end

Now in your controller you can do:

@users = User.order('RANDOM()').with_avatar.with_posts

Hope this helps!

Upvotes: 2

Sebasti&#225;n Palma
Sebasti&#225;n Palma

Reputation: 33491

You can just use the association between User and Post (if Post is your model), this will check whether the foreign key and primary key in both cases match, what eventually checks if a User has recorded some Post:

User.order('RANDOM()').joins(:posts).where.not(avatar: nil)
# SELECT "users".*
# FROM "users"
# INNER JOIN "posts" ON
# "posts"."user_id" = "users"."id"
# WHERE ("users"."avatar" IS NOT NULL) ORDER BY RANDOM()

In other way you could check if the id of the associated model (the one that depends in User) is not nil, that means, it exists as an associated record, like:

User.order('RANDOM()').joins(:posts).where.not(avatar: nil)
                                    .where.not(posts: { id: nil })

Upvotes: 5

Related Questions