CODEWITHSUNDEEP
sqlruby-on-railsruby
mr_muscle
mr_muscle

Reputation: 2900

Rails or SQL query to fetch user association data

I've got three models with below fields (each field after #):

class User < ApplicationRecord
  has_many :user_tags, dependent: :destroy
end

class UserTag < ApplicationRecord
  belongs_to :user
end

class JourneyTag < ApplicationRecord
  belongs_to :journey
end

I want to find all Journeys with tags corresponding to the user's tags - user.user_tags. How to fetch such data?

What I was trying to do:

current_user.user_tags.each do |user_tag|
  JourneyTag.where(cms_tag_id: user_tag.cms_tag_id)
end

Which should gives me JourneyTag with the same cms_tag_id as user.user_tags has and then I want to grab this collection of JourneyTag to find Journey. But above query is unuseful because it always returns some JourneyTag even if it's not matched with user_tag.cms_tag_id

Upvotes: 3

Views: 225

Answers (1)

kiddorails
kiddorails

Reputation: 13014

Try:

Journey.joins(:journey_tags).where(journey_tags: { cms_tag_id: current_user.user_tags.pluck(:cms_tag_id) })

Breaking it down:

current_user.user_tags #=> returns all user tag ids associated 
current_user.user_tags.pluck(:cms_tag_id) #=> gives all associated cms_tag_id in single SELECT `cms_tag_id` query 
journey_tags: { cms_tag_id: current_user.user_tags.select(:cms_tag_id) } #=> executes matching WHERE against JourneyTags
Journey.joins(:journey_tags) #=> filtered JourneyTags joins with Journey

Alternatively, you can try below with manual joins as:

Journey.joins(:journey_tags).joins('INNER JOIN user_tags on user_tags.cms_tag_id = journey_tags.cms_tag_id INNER JOIN users on user_tags.user_id = users.id')

Upvotes: 2

Related Questions