Hello, I need help with building more complex queries across multiple tables.
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?