Query on model associated as belongs_to in ActiveRecord (Is there a better way)

Question

I have two models

class InterestingGuest < ApplicationRecord
  # column_name
  # recommended_to
  # recommendee

  belongs_to :recommended_to, class_name: 'User'
  belongs_to :recommendee, class_name: 'User'
end

class User < ApplicationRecord
  # column_name
  # membership_level : ['Member', 'Guest']

  has_many :guest_recommendations, class_name: 'InterestingGuest',
                                   foreign_key: 'recommended_to_id',
                                   dependent: :destroy

  has_many :recommendees, class_name: 'InterestingGuest',
                          foreign_key: 'recommendee_id',
                          dependent: :destroy
end

I'm trying to find all the recommendees where membership_level is Guest

the query I tried is

recommendees = InterestingGuest.where(recommended_to_id: user_id))
                               .pluck(:recommendee_id)
guests = User.where(id: recommendees).where(membership_level: 'Guest')

Even though it serves the purpose but is there a better way to achieve the same in a single query

You can try with this query, I created this query according to your post. `InterestingGuest.join("LEFT OUTER JOIN users on users.user_id = interesting_guests.recommendee_id where users.membership_level = 'Guest'")` — Rohit Lingayat, Mar 30 '20 at 10:25
Hey @RohitLingayat thanks for the response but this will end up running the query on every `user` whereas I'm trying to find all the `recommendees` whose `membership_level` is guest and are `recommended_to` a specific user. — Abhinay, Mar 30 '20 at 11:07
it will not execute the query for every user, it will execute only on matched users. There is left join on `users.user_id = interesting_guests.recommendee_id` and in the same way you can also add the `recommended_to` in where clause — Rohit Lingayat, Mar 30 '20 at 11:22
Ahh Got it, thanks. this query works: `InterestingGuest.joins("LEFT OUTER JOIN users on users.id = interesting_guests.recommendee_id where users.membership_level = 'Guest' and recommended_to_id = 1")` — Abhinay, Mar 30 '20 at 12:03
(y) I am putting this comment as an answer. You can upvote if your problem is resolved by this query — Rohit Lingayat, Mar 31 '20 at 08:43

score 1 · Accepted Answer · answered Mar 31 '20 at 08:50

You can use LEFT OUTER JOIN in this scenario.

Try:

InterestingGuest.join("LEFT OUTER JOIN users on users.user_id = interesting_guests.recommendee_id where users.membership_level = 'Guest'")

Here user_id means recommendee_id as per the association which you mentioned in the class.

score 0 · Answer 2 · answered Mar 30 '20 at 08:44

0

Rails: ActiveRecord query based on association value should help you out.

Try:

InterestingGuest.joins(:users).where(users:{ membership_level: 'Guest' })

or

InterestingGuest.joins(:users).where('users.membership_level' = 'Guest')

Personaly I think the first looks neater.

Official docs: https://guides.rubyonrails.org/active_record_querying.html#joining-tables

answered Mar 30 '20 at 08:44

Mark

6,112
4
21
46

Thank you for the response, but joining `users` on `InterestingGuest` will not work since the association is via `recommended_to` and `recommendee` – Abhinay Mar 30 '20 at 09:57
Also, the query I'm trying here is to get `recommendees` whose `membership_level` is `guest` and are `recommended_to` a specific user – Abhinay Mar 30 '20 at 10:00

Query on model associated as belongs_to in ActiveRecord (Is there a better way)

2 Answers2