Complex joins and raw sql query problem

Hello, I need help with building more complex queries across multiple tables.

Use case
I’m using the pg_trgm function to search for similar strings in segments and then get their translations (target segments) via translation records. Then I add the user info to the result.

Repositories and associations configuration

class TranslationRecordRepository < Hanami::Repository
  associations do
    belongs_to :segment, as: :source_segment
    belongs_to :segment, as: :target_segment
    belongs_to :user

class SegmentRepository < Hanami::Repository
  associations do
    has_many :translation_records
    has_many :segments, through: :translation_records, as: :target_segments
    belongs_to :language

class UserRepository < Hanami::Repository
  associations do
    has_many :translation_records

The first problem:
To be able to leverage the index support offered by the pg_trgm I need to use the % operator which I can’t seem to do with Ruby/Hanami/ROM. I therefore went the “raw sql” way as you can see below.

The “raw sql” solution (actual strings for example):

def find_by_segment_match(source_text_for_lookup, source_lang, sim_score)
  .read("set pg_trgm.similarity_threshold TO 0.4;
  SELECT *, SIMILARITY(segments.content, 'A blue vehicle is here.') AS similarity
  FROM segments
  JOIN translation_records
  ON = translation_records.source_segment_id
  AND segments.content % 'A blue vehicle is here.'
  LEFT JOIN segments AS target_segments
  ON translation_records.target_segment_id =
  AND translation_records.language_combination = '2_1'
  JOIN users
  ON translation_records.created_by =
  ORDER BY SIMILARITY(segments.content, 'A blue vehicle is here.') DESC;").map_to(FoundTranslation)

The second problem:
I can’t map the joined query result to a placeholder entity (FoundTranslation) I have just for this case.
I always get back only the Segment entity.

How can I translate this raw sql to Hanami/ROM syntax or map the query result to a placeholder entity?
I have been looking for some more complex examples to do joins on multiple tables but couldn’t find any.
Following the docs simple joins and aggregates I can do, but I’d like to avoid multiple queries, which is what I do now and it is resource intensive.

Can someone help with either possible solution to this problem?

Best, seba

I’ve been digging a bit deeper and from the docs and resources online I gather I should be able to map a query result to either a Hanami::Entity or a Dry::Struct. But in either case I always get back only the Segment entity with the above raw sql call.

i think i might have to bump into the same issue in the past

as i remember (but i’m not sure i do :stuck_out_tongue: ) i also had troubles with mapping to different Entities. I guess it’s not working in Hanami 1.3.

For me, the solution was migrating to ROM 5.2 :slight_smile:

@synu Thank you for confirming. I tend not to deviate from the default configuration, it’s the same with this issue. What I ended up doing - actually just did it today - is I set up an entity and a repository without the DB table. I then use this a vehicle to get the raw sql back. There’s no mapping like in the docs. I don’t know how much of “Hanami” way this is :grinning: but it’s working.

Best, seba