I am using following versions in Gemfile
gem 'rom', '~> 5.3' gem 'rom-sql', '~> 3.6', '>= 3.6.1'
Following are my relations info:
Relation_1 < ROM::Relation[:sql] associations do has_many :relation_3 has_many :relation_2, through: :relation_3 end
Relation_2 < ROM::Relation[:sql] associations do belongs_to :relation_4 end
# many-to-many Relation_3 < ROM::Relation[:sql] associations do belongs_to :relation_1 belongs_to :relation_2 end
Relation_4 < ROM::Relation[:sql]
Relation_1 I want to add a method which should implement a query like following
SELECT relation_1.id, relation_1.name_val relation_4.name_val FROM relation_1 INNER JOIN ON relation_3 ON ( relation_1.id = relation_3.relation_1_id ) INNER JOIN ON relation_2 ON ( relation_3.relation_2_id = relation_2.id ) INNER JOIN ON relation_4 ON ( relation_2.relation_4_id = relation_4.id ) WHERE relation_1.id = relation_1_id_arg
As can be seen the query utilizes nested joins. In Rails I can achieve it in following manner:
but I am unable to figure out how to do in rom-sql.
def my_nested_query(relation_1_id_arg) rel = join(relation_2) rel = rel.join(:relation_4, <??? how to specify here that this join should happen on relation_2.relation_4_id and relation_4.id ???>) end
join(relation_2).join(:relation_4) shows following error:
ROM::ElementNotFoundError: :relation_4 doesn't exist in ROM::AssociationSet[:relation_1] registry from ..../gems/rom-core-5.3.0/lib/rom/registry.rb:91:in `block in fetch'
which is obvious because
Relation_1’s association block doesn’t define its relation with
Relation_4 as they are not directly related.
Sequel documentation does illustrates nested joins like following at querying.rdoc
Album.join(:artists, id: :artist_id). join(:members, artist_id: :id) # SELECT * FROM albums # INNER JOIN artists ON (artists.id = albums.artist_id) # INNER JOIN members ON (members.artist_id = artists.id)
Album.join(:artists, id: :artist_id). join(:tracks, album_id: :id) # SELECT * FROM albums # INNER JOIN artists ON (artists.id = albums.artist_id) # INNER JOIN tracks ON (tracks.album_id = artists.id)
but from the documentation at Module: ROM::SQL::Relation::Reading — Documentation by YARD 0.9.24 and ROM - Joins it is not clear how do I achieve my desired need.
Can anybody please guide me on how do I implement my desired need?