Complex joins and raw sql query problem

[sebastjan_hribar]

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
  end
end

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

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

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)
  segments
  .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 segments.id = 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 = target_segments.id
  AND translation_records.language_combination = '2_1'
  JOIN users
  ON translation_records.created_by = users.id
  ORDER BY SIMILARITY(segments.content, 'A blue vehicle is here.') DESC;").map_to(FoundTranslation)
end

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

[sebastjan_hribar]

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.

[synu]

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

Connecting two Repositories to the same DB table

Hey, i’m writing app in Hanami 1.3 and have hard time trying to connect two repositories to the same table. Is it doable in Hanami 1.3? I dug a little bit and it looks to me that either rom in version 3 doesn’t support this, or it’s some missing feature in Hanami-model. I tried to hack around, and managed to connect two Repository classes using the same relation (had to make some changes in ROM gem), but i still cannot make second repository to map data to different Entity. Again, does anyon…

as i remember (but i’m not sure i do ) 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

[sebastjan_hribar]

@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 but it’s working.

Best, seba