How to retrieve data from multiple tables by multiple foreign keys in one query in ActiveRecord?-CodePudding

There is a model named Track:

class Track < ApplicationRecord
  has_many :profile_tracks
  has_many :favorite_tracks
  has_many :bookmark_tracks
  has_many :listened_tracks
end

And there is a model named Profile:

class Profile < ApplicationRecord
  has_many :profile_tracks
  has_many :favorite_tracks
  has_many :bookmark_tracks
  has_many :listened_tracks
end

I need to retrieve entries from those tables that share the same track_id and profile_id values. E.g.:

ProfileTrack.find_by(track_id: 1, profile_id: 1)
 
FavoriteTrack.find_by(track_id: 1, profile_id: 1)
 
BookmarkTrack.find_by(track_id: 1, profile_id: 1)
 
ListenedTrack.find_by(track_id: 1, profile_id: 1)

track = Track.find_by(id: 1)

track.profile_tracks.find_by(profile_id: 1)
 
track.favorite_tracks.find_by(profile_id: 1)
 
track.bookmark_tracks.find_by(profile_id: 1)
 
track.listened_tracks.find_by(profile_id: 1)

profile = Profile.find_by(id: 1)

profile.profile_tracks.find_by(track_id: 1)
 
profile.favorite_tracks.find_by(track_id: 1)
 
profile.bookmark_tracks.find_by(track_id: 1)
 
profile.listened_tracks.find_by(track_id: 1)

and return something like this:

{
  profile_track_id: some_id,
  favorite_track_id: some_id,
  bookmark_track_id: some_id,
  listened_track_id: some_id
}

or ActiveRecord model with these ids.

How can I do it in one query?

CodePudding user response：

If I understand correctly you are trying to create a some kind of music playlist app? You might want to change your has_many in Track to belongs_to, but it depends on your other models. Anyway, you should be able to access them directly by using joins.

track = Track.joins(:profile_tracks, :favorite_tracks, :bookmark_tracks, 
                    :listened_tracks).find_by(id: 1)

You can use find(1) and this will raise an error if not found, find_by(id:1) will return nil. Then in your hash

{
  profile_track_id: track.profile_track_id,
  favorite_track_id: track.favorite_track_id,
  bookmark_track_id: track.bookmark_track_id,
  listened_track_id: track.listened_track_id
}

Update

To align with the question, as the above was wrong answer but still might be useful.

track = Track.joins(:profile_tracks, :favorite_tracks, :bookmark_tracks, 
                    :listened_tracks, :profiles).where(profiles: {id: 1})

This will produce below query

SELECT "tracks".* FROM "tracks" 
INNER JOIN "profile_tracks" ON "profile_tracks"."track_id" = "tracks"."id"
... 
INNER JOIN "profiles" ON "profiles"."track_id" = "tracks"."id"
WHERE "profiles"."id" = $1

Update 2

Thanks for pointing out that the relation was non-existing between tracks and profiles, how about rearranging the structure?

class Profile < ApplicationRecord
  has_many :profile_tracks
  has_many :tracks, through: :profile_tracks
end

class ProfileTrack < ApplicationRecord
  belongs_to :profile
  has_many :tracks
end

class Track < ApplicationRecord
  belongs_to :profile_track
end

that would allow you to access both track_ids and profile_track_ids from Profile

CodePudding user response：

Did it with raw SQL and UNION:

track = Track.find_by(id: 1)

a =
  track
  .profile_tracks
  .where(profile_id: 1)
  .select("id as value, 'profile_track_id' as key")
  .to_sql

b =
  track
  .favorite_tracks
  .where(profile_id: 1)
  .select("id as value, 'favorite_track_id' as key")
  .to_sql

c =
  track
  .bookmark_tracks
  .where(profile_id: 1)
  .select("id as value, 'bookmark_track_id' as key")
  .to_sql

d =
  track
  .listened_tracks
  .where(profile_id: 1)
  .select("id as value, 'listened_track_id' as key")
  .to_sql

ActiveRecord::Base
  .connection
  .execute("#{a} UNION #{b} UNION #{c} UNION #{d}")
  .to_a
  .map {|a| { a['key'] => a['value']}}
  .inject(:merge)