Implementing nested joins using join method

Issues
1

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]

Now in 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:

Relation_1.joins(relation_2: [:relation_4])

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

Doing 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?

Thanks.

1 Like
2

@jignesh, I ran into the same problem. Is it possible to find out how you solved it?
I agree with you about the vague description in the official documentation of JOIN usage. Also combine node and associations.
Developers, please respond to the issues

3

@AndrewTehan

Is it possible to find out how you solved it?

I had no choice except to split my data gathering approach into multiple steps (in other words manually firing multiple queries).

Thanks.