full_text ranking search from postgres with ModelLoader

[abarbosa94]

Hi there,

I'm trying to use a declarative syntax from ModelLoader with Gino to reproduce the following query:

results = await db.all(
            db.text(
                f"""
                    select
                      d.code as code,
                      l.title as title,
                      d.scope as scope,
                      array_agg(tl.name) as tags,
                      coalesce(l.text, '') as text,
                      ts_rank_cd((
                        setweight(to_tsvector('portuguese', coalesce(l.title, '')), 'A')
                        || setweight(to_tsvector('portuguese', coalesce(l.text, '')), 'B')
                      ), query, 1) as rank,
                      l.link as link
                    from document_localization l
                      left join document d on l.document_code = d.code
                      left join document_x_tag_join dxt on dxt.document_code = d.code
                      left join tag t on t.code = dxt.tag_code
                      left join tag_localization tl on t.code = tl.tag_code
                        and tl.locale_code = l.locale_code
                      , to_tsquery('portuguese', '{search_expr}') query
                    where
                      l.locale_code = '{locale_code}'
                      and (
                        setweight(to_tsvector('portuguese', coalesce(l.title, '')), 'A')
                        || setweight(to_tsvector('portuguese', coalesce(l.text, '')), 'B')
                      ) @@ query
                      {"" if scope is None else f" and d.scope = '{scope}'"}
                    group by d.code, d.scope, l.title, l.text, query, l.link
                    order by rank desc
                    {"" if size is None else f" limit {size}"}
                """
            )
        )
        return results

But I'm having real trouble to use the rank method with loaders. This is the query that I got so far:

query = (
            DocumentLocalization.distinct(DocumentLocalization.document_code)
            .load(
                document=Document.distinct(Document.code).load(
                    load_document_x_tag_join=DocumentXTagJoin.load(
                        tag_localization=TagLocalization.load(
                            tag=Tag.load(
                                tagging_category_localization=TaggingCategoryLocalization.on(
                                    Tag.tagging_category_code
                                    == TaggingCategoryLocalization.tagging_category_code
                                )
                            )
                        )
                    )
                )
            )
            .where(DocumentLocalization.locale_code == locale_code)
            .where(TagLocalization.locale_code == locale_code)
            .where(TaggingCategoryLocalization.locale_code == locale_code)
            .where(Tag.active == True)  # noqa
            .where(Document.active == True)  # noqa
        )

        if scope is not None:
            query = query.where(Document.scope == scope)

        if size is not None:
            query = query.limit(size)

        return await query.gino.all()

I've noticed that Gino does support functions but when I tried to use func.set_weight(func.ts_vector('text'), 'A').concat(func.set_weight(func.ts_vector('title'), 'B')).match(func.ts_query('search_expr')) my code breaks.

Moreover, I have no idea how I adapt the func.ts_rank_cd for this scenario

Could Anyone please help?

Thanks a lot