0

I am trying to create an instance Object of all Users that have one or more Video in my controller, so there is one User that has many videos. So I have a User.rb model that has_many :videos, and my Video.rb model that belongs_to :user. I want to select all the users that have atleast one video.

I am really new at querying SQL so I'm not sure how to join tables or if I even need to.

ex:

@users = User.where(user.video not nil?)
which_part
  • 792
  • 3
  • 11
  • 26
TacoMaster6000
  • 304
  • 3
  • 12
  • 1
    Note for @KirtiThorat is correct but if you are iterating over the videos It might be better to use `includes(:videos)` or `eager_load(:videos)` so that you do not run into an n+1 issues. Alternatively you could use a `select` and `group` clause as well. – engineersmnky Jul 31 '14 at 18:03
  • @engineersmnky Thanks for the pointer. Added it in my answer. – Kirti Thorat Aug 01 '14 at 13:52

2 Answers2

3

I want to select all the users that have atleast one video.

All you need to do is use joins method

@users = User.joins(:videos)

It will retrieve all users which have associated video(s) i.e., users without any associated videos would not be part of the results and query formed would be:

SELECT users.* FROM users
  INNER JOIN videos ON videos.user_id = users.id

As @engineersmnky pointed out in this comment, if you are planning to iterate over the users retrieved in @users and then again go through the videos of each user then it would be a better idea to go for eager loading rather than joins to avoid the n+1 queries problem. In that case your code should be:

@users = User.includes(:videos).where("videos.id IS NOT NULL")

and for Rails 4 and above

@users = User.includes(:videos).where.not(videos: {id: nil})
Community
  • 1
  • 1
Kirti Thorat
  • 52,578
  • 9
  • 101
  • 108
1

Something like this

User.includes(:videos).where("videos.user_id is not null")
Pavan
  • 33,316
  • 7
  • 50
  • 76