Ash Elixir•2y ago

Fragment with multiple 'from' items.

I'm trying to implement search similar to: https://leandronsp.com/a-powerful-full-text-search-in-postgresql-in-less-than-20-lines where the query looks like this:

SELECT 
    courses.id,
    courses.title,
    courses.description,
    rank_title,
    rank_description,
    similarity
FROM 
    courses, 
    to_tsvector(courses.title || courses.description) document,
    to_tsquery('curse') query,
    NULLIF(ts_rank(to_tsvector(courses.title), query), 0) rank_title,
    NULLIF(ts_rank(to_tsvector(courses.description), query), 0) rank_description,
    SIMILARITY('curse', courses.title || courses.description) similarity
WHERE query @@ document OR similarity > 0
ORDER BY rank_title, rank_description, similarity DESC NULLS LAST

SELECT 
    courses.id,
    courses.title,
    courses.description,
    rank_title,
    rank_description,
    similarity
FROM 
    courses, 
    to_tsvector(courses.title || courses.description) document,
    to_tsquery('curse') query,
    NULLIF(ts_rank(to_tsvector(courses.title), query), 0) rank_title,
    NULLIF(ts_rank(to_tsvector(courses.description), query), 0) rank_description,
    SIMILARITY('curse', courses.title || courses.description) similarity
WHERE query @@ document OR similarity > 0
ORDER BY rank_title, rank_description, similarity DESC NULLS LAST

I think I would write a fragment like:

      filter expr(
               fragment(
                 """
                 query @@ document OR similarity > 0
                 """
               )
             )

      filter expr(
               fragment(
                 """
                 query @@ document OR similarity > 0
                 """
               )
             )

on the read action. However for the FROM side of things, do you think those items should be in a custom calculation? I had tried:

    calculate :query,
              :string,
              expr(
                fragment(
                  """
                  websearch_to_tsquery(?::text::regconfig, ?)
                  """,
                  ^arg(:doc_language),
                  ^arg(:search_term)
                )
              )

    calculate :query,
              :string,
              expr(
                fragment(
                  """
                  websearch_to_tsquery(?::text::regconfig, ?)
                  """,
                  ^arg(:doc_language),
                  ^arg(:search_term)
                )
              )

but get this:

     
filter: #Ash.Filter<fragment(
       {:raw, "query @@ document OR similarity > 0\n"}
     )>,
     errors: [
       %Ash.Error.Unknown.UnknownError{
         error: "** (Postgrex.Error) ERROR 42703 (undefined_column) column \"query\" does not exist\n\n    query: SELECT l0.\"id\", l0.\"title\", l0.\"bcp47\", l0.\"doc_language\", l0.\"language_id\" FROM \"language_translations\" AS l0 WHERE (query @@ document OR similarity > 0\n)",

     
filter: #Ash.Filter<fragment(
       {:raw, "query @@ document OR similarity > 0\n"}
     )>,
     errors: [
       %Ash.Error.Unknown.UnknownError{
         error: "** (Postgrex.Error) ERROR 42703 (undefined_column) column \"query\" does not exist\n\n    query: SELECT l0.\"id\", l0.\"title\", l0.\"bcp47\", l0.\"doc_language\", l0.\"language_id\" FROM \"language_translations\" AS l0 WHERE (query @@ document OR similarity > 0\n)",

which makes sense but I"m wondering how to make them work together.

28 Replies

ZachDaniel•2y ago

what is websearch_to_tsquery? oh, thats the postgres function Regardless, the problem is referencing fields raw in the fragment

"query @@ document OR similarity > 0\n"

"query @@ document OR similarity > 0\n"

    calculate :query,
              :string,
              expr(
                fragment(
                  """
                  websearch_to_tsquery(?::text::regconfig, ?)
                  """,
                  ^arg(:doc_language),
                  ^arg(:search_term)
                )
              ) do
  argument :doc_language, :string, allow_nil?: false
  argument :search_term, :string, allow_nil?: false
end

    calculate :query,
              :string,
              expr(
                fragment(
                  """
                  websearch_to_tsquery(?::text::regconfig, ?)
                  """,
                  ^arg(:doc_language),
                  ^arg(:search_term)
                )
              ) do
  argument :doc_language, :string, allow_nil?: false
  argument :search_term, :string, allow_nil?: false
end

So arguments referenced in calculations go on the calculation itself And then when using them you provide them as a keyword list

fragment("? @@ ? OR ? > 0", query(doc_language: "doc_language", search_term: "search_term"), document, ....)

fragment("? @@ ? OR ? > 0", query(doc_language: "doc_language", search_term: "search_term"), document, ....)

gdub01OP•2y ago

awesome ty I'll try that out!

ZachDaniel•2y ago

So you can compose calculations using that tool Just remember that if you're using AshGraphql and the like to mark calculations that are just meant for composing with other calculations as private

gdub01OP•2y ago

brooooo it worked!! Man this is awesome. Thank you so much! Below is the calcuations and filters used.

      filter expr(
               fragment(
                 "? @@ ? OR ? > 0",
                 query(doc_language: arg(:doc_language), search_term: arg(:search_term)),
                 document,
                 similarity(search_term: arg(:search_term))
               )
             )

      filter expr(
               fragment(
                 "? @@ ? OR ? > 0",
                 query(doc_language: arg(:doc_language), search_term: arg(:search_term)),
                 document,
                 similarity(search_term: arg(:search_term))
               )
             )

  calculations do
    calculate :document,
              :string,
              expr(
                fragment("""
                to_tsvector(lang_to_regconfig(doc_language),coalesce(title, ''))
                """)
              )

    calculate :query,
              :string,
              expr(
                fragment(
                  """
                  websearch_to_tsquery(?::text::regconfig, ?)
                  """,
                  ^arg(:doc_language),
                  ^arg(:search_term)
                )
              ) do
      argument :doc_language, :string, allow_nil?: false
      argument :search_term, :string, allow_nil?: false
    end

    calculate :similarity,
              :float,
              expr(
                fragment(
                  """
                  SIMILARITY(?, title)
                  """,
                  ^arg(:search_term)
                )
              ) do
      argument :search_term, :string, allow_nil?: false
    end

    calculate :rank_title,
              :string,
              expr(
                fragment("""
                NULLIF(ts_rank(to_tsvector(title), query), 0)
                """)
              )
  end

  calculations do
    calculate :document,
              :string,
              expr(
                fragment("""
                to_tsvector(lang_to_regconfig(doc_language),coalesce(title, ''))
                """)
              )

    calculate :query,
              :string,
              expr(
                fragment(
                  """
                  websearch_to_tsquery(?::text::regconfig, ?)
                  """,
                  ^arg(:doc_language),
                  ^arg(:search_term)
                )
              ) do
      argument :doc_language, :string, allow_nil?: false
      argument :search_term, :string, allow_nil?: false
    end

    calculate :similarity,
              :float,
              expr(
                fragment(
                  """
                  SIMILARITY(?, title)
                  """,
                  ^arg(:search_term)
                )
              ) do
      argument :search_term, :string, allow_nil?: false
    end

    calculate :rank_title,
              :string,
              expr(
                fragment("""
                NULLIF(ts_rank(to_tsvector(title), query), 0)
                """)
              )
  end

Would sorting on this be possible?

prepare(build(sort: [:rank_title, similarity(search_term: arg(:search_term)): :desc]))

prepare(build(sort: [:rank_title, similarity(search_term: arg(:search_term)): :desc]))

ZachDaniel•2y ago

yes

prepare(build(sort: [:rank_title, similarity: {:desc, %{search_term: arg(:search_term)}}]))

prepare(build(sort: [:rank_title, similarity: {:desc, %{search_term: arg(:search_term)}}]))

its a bit of a weird format calc_name: %{...input} or calc_name: {:order, %{...input}}

gdub01OP•2y ago

Thanks so much @Zach Daniel ! That actually worked perfectly for similarity! The rank_title is giving me a problem. I've tried solving it 3 ways without luck: 1. This way recalculates 'query' in the rank_title calculation in an attempt to return the rank:

    calculate :rank_title,
              :string,
              expr(
                fragment(
                  """
                  websearch_to_tsquery(?::text::regconfig, ?) query
                  NULLIF(ts_rank(to_tsvector(title), query), 0)
                  """,
                  ^arg(:doc_language),
                  ^arg(:search_term)
                )
              ) do
      argument :doc_language, :string, allow_nil?: false
      argument :search_term, :string, allow_nil?: false
    end

    calculate :rank_title,
              :string,
              expr(
                fragment(
                  """
                  websearch_to_tsquery(?::text::regconfig, ?) query
                  NULLIF(ts_rank(to_tsvector(title), query), 0)
                  """,
                  ^arg(:doc_language),
                  ^arg(:search_term)
                )
              ) do
      argument :doc_language, :string, allow_nil?: false
      argument :search_term, :string, allow_nil?: false
    end

but it fails where I'm trying to bind the word 'query' there. I've also tried:

    calculate :rank_title,
              :string,
              expr(
                fragment(
                  """
                  NULLIF(ts_rank(to_tsvector(title), ?), 0)
                  """,
                  ^arg(:query)
                )
              ) do
      argument :query, :string
    end

    calculate :rank_title,
              :string,
              expr(
                fragment(
                  """
                  NULLIF(ts_rank(to_tsvector(title), ?), 0)
                  """,
                  ^arg(:query)
                )
              ) do
      argument :query, :string
    end

and then use it like:

      prepare(
        build(
          sort: [
            rank_title:
              {:asc,
               %{query: query(doc_language: arg(:doc_language), search_term: arg(:search_term))}},
            similarity: {:desc_nils_last, %{search_term: arg(:search_term)}}
          ]
        )
      )

      prepare(
        build(
          sort: [
            rank_title:
              {:asc,
               %{query: query(doc_language: arg(:doc_language), search_term: arg(:search_term))}},
            similarity: {:desc_nils_last, %{search_term: arg(:search_term)}}
          ]
        )
      )

but then in the prepare step I get: undefined function query/1 (there is no such import) 3. I've also tried prepare(build(load: query and having rank_title defined as:

    calculate :rank_title,
              :string,
              expr(
                fragment(
                  """
                  NULLIF(ts_rank(to_tsvector(title), query), 0)
                  """
                )
              )
    end

    calculate :rank_title,
              :string,
              expr(
                fragment(
                  """
                  NULLIF(ts_rank(to_tsvector(title), query), 0)
                  """
                )
              )
    end

but query isn't around.

ZachDaniel•2y ago

🤔 So, the main issue with the first attempt is that you can't nest calculations in the sort like that you can only provide arguments to one calculation well, technically you can, and maybe this is what you want to do, actually

  argument :query, :string, allow_expr?: true

  argument :query, :string, allow_expr?: true

      prepare(
        build(
          sort: [
            rank_title:
              {:asc,
               %{query: expr(query(doc_language: arg(:doc_language), search_term: arg(:search_term)))}},
            similarity: {:desc_nils_last, %{search_term: arg(:search_term)}}
          ]
        )
      )

      prepare(
        build(
          sort: [
            rank_title:
              {:asc,
               %{query: expr(query(doc_language: arg(:doc_language), search_term: arg(:search_term)))}},
            similarity: {:desc_nils_last, %{search_term: arg(:search_term)}}
          ]
        )
      )

So that is one way the other way is to make a calculation like

calculate :rank_title, :string, expr(
  fragment("", query(..., ))
) do
  argument ...
end

calculate :rank_title, :string, expr(
  fragment("", query(..., ))
) do
  argument ...
end

where it calls query automatically and passes the arguments down I would probably choose the latter 🙂

gdub01OP•2y ago

ty! I'm trying them both right now Weirdly I'm getting:

"** (Postgrex.Error) ERROR 42883 (undefined_function) function ts_rank(tsvector, text) does not exist\n\n    query: SELECT l0.\"id\", l0.\"title\", l0.\"bcp47\", l0.\"doc_language\", l0.\"language_id\" FROM \"language_translations\" AS l0 WHERE (websearch_to_tsquery($1::text::regconfig, $2)\n::text @@ to_tsvector(lang_to_regconfig(doc_language),coalesce(title, ''))\n::text OR SIMILARITY($3, title)\n::float > 0\n) ORDER BY NULLIF(ts_rank(to_tsvector(title), websearch_to_tsquery($4::text::regconfig, $5)\n::text), 0)::text, SIMILARITY($6, title)\n::float DESC NULLS LAST\n\n    hint: No function matches the given name and argument types. You might need to add explicit type casts."

"** (Postgrex.Error) ERROR 42883 (undefined_function) function ts_rank(tsvector, text) does not exist\n\n    query: SELECT l0.\"id\", l0.\"title\", l0.\"bcp47\", l0.\"doc_language\", l0.\"language_id\" FROM \"language_translations\" AS l0 WHERE (websearch_to_tsquery($1::text::regconfig, $2)\n::text @@ to_tsvector(lang_to_regconfig(doc_language),coalesce(title, ''))\n::text OR SIMILARITY($3, title)\n::float > 0\n) ORDER BY NULLIF(ts_rank(to_tsvector(title), websearch_to_tsquery($4::text::regconfig, $5)\n::text), 0)::text, SIMILARITY($6, title)\n::float DESC NULLS LAST\n\n    hint: No function matches the given name and argument types. You might need to add explicit type casts."

ZachDaniel•2y ago

Ah, 🤔 yeah, right its because the argument :string 🤔 🤔 🤔 one sec maybe can I see what exactly you've set up that gives you that?

gdub01OP•2y ago

I seemed to have received that same error after trying both ways just fyi. below is the current setup:

    calculate :rank_title,
              :string,
              expr(
                fragment(
                  "NULLIF(ts_rank(to_tsvector(title), ?), 0)",
                  query(doc_language: arg(:doc_language), search_term: arg(:search_term))
                )
              ) do
      argument :doc_language, :string, allow_nil?: false
      argument :search_term, :string, allow_nil?: false
    end

    calculate :rank_title,
              :string,
              expr(
                fragment(
                  "NULLIF(ts_rank(to_tsvector(title), ?), 0)",
                  query(doc_language: arg(:doc_language), search_term: arg(:search_term))
                )
              ) do
      argument :doc_language, :string, allow_nil?: false
      argument :search_term, :string, allow_nil?: false
    end

      prepare(
        build(
          sort: [
            rank_title:
              {:asc, %{doc_language: arg(:doc_language), search_term: arg(:search_term)}},
            similarity: {:desc_nils_last, %{search_term: arg(:search_term)}}
          ]
        )
      )

      prepare(
        build(
          sort: [
            rank_title:
              {:asc, %{doc_language: arg(:doc_language), search_term: arg(:search_term)}},
            similarity: {:desc_nils_last, %{search_term: arg(:search_term)}}
          ]
        )
      )

    calculate :query,
              :string,
              expr(
                fragment(
                  """
                  websearch_to_tsquery(?::text::regconfig, ?)
                  """,
                  ^arg(:doc_language),
                  ^arg(:search_term)
                )
              ) do
      argument :doc_language, :string, allow_nil?: false
      argument :search_term, :string, allow_nil?: false
    end

    calculate :query,
              :string,
              expr(
                fragment(
                  """
                  websearch_to_tsquery(?::text::regconfig, ?)
                  """,
                  ^arg(:doc_language),
                  ^arg(:search_term)
                )
              ) do
      argument :doc_language, :string, allow_nil?: false
      argument :search_term, :string, allow_nil?: false
    end

This code runs in postgres directly:

SELECT 
    id,
    title,
    rank_title,
    similarity
 FROM 
 "public"."language_translations",
to_tsvector(lang_to_regconfig(doc_language),coalesce(title, '')) DOCUMENT,
 websearch_to_tsquery('english'::text::regconfig, 'glis') query,
 NULLIF(ts_rank(to_tsvector(title), query), 0) rank_title,
 SIMILARITY('glis', title) similarity
 WHERE query @@ document OR similarity > 0
ORDER BY rank_title, similarity DESC NULLS LAST

SELECT 
    id,
    title,
    rank_title,
    similarity
 FROM 
 "public"."language_translations",
to_tsvector(lang_to_regconfig(doc_language),coalesce(title, '')) DOCUMENT,
 websearch_to_tsquery('english'::text::regconfig, 'glis') query,
 NULLIF(ts_rank(to_tsvector(title), query), 0) rank_title,
 SIMILARITY('glis', title) similarity
 WHERE query @@ document OR similarity > 0
ORDER BY rank_title, similarity DESC NULLS LAST

so I know ts_rank exists.

ZachDaniel•2y ago

Yeah, that makes sense The basic problem is this:

    calculate :query,
              :string, # <- right here
              expr(
                fragment(
                  """
                  websearch_to_tsquery(?::text::regconfig, ?)
                  """,
                  ^arg(:doc_language),
                  ^arg(:search_term)
                )
              ) do
      argument :doc_language, :string, allow_nil?: false
      argument :search_term, :string, allow_nil?: false
    end

    calculate :query,
              :string, # <- right here
              expr(
                fragment(
                  """
                  websearch_to_tsquery(?::text::regconfig, ?)
                  """,
                  ^arg(:doc_language),
                  ^arg(:search_term)
                )
              ) do
      argument :doc_language, :string, allow_nil?: false
      argument :search_term, :string, allow_nil?: false
    end

The calculation says it returns a :string And so we tell postgres its a ::text because that is how we handle strings

gdub01OP•2y ago

Got it. Where it should be either a float or null. I should just be able to switch it to float then

gdub01OP•2y ago

Ah no luck.

 "** (Postgrex.Error) ERROR 42883 (undefined_function) function ts_rank(tsvector, text) does not exist\n\n    query: SELECT l0.\"id\", l0.\"title\", l0.\"bcp47\", l0.\"doc_language\", l0.\"language_id\" FROM \"language_translations\" AS l0 WHERE (websearch_to_tsquery($1::text::regconfig, $2)\n::text @@ to_tsvector(lang_to_regconfig(doc_language),coalesce(title, ''))\n::text OR SIMILARITY($3, title)\n::float > 0\n) ORDER BY NULLIF(ts_rank(to_tsvector(title), websearch_to_tsquery($4::text::regconfig, $5)\n::text), 0)::float, SIMILARITY($6, title)\n::float DESC NULLS LAST\n\n    hint: No function matches the given name and argument types. You might need to add explicit type casts."

 "** (Postgrex.Error) ERROR 42883 (undefined_function) function ts_rank(tsvector, text) does not exist\n\n    query: SELECT l0.\"id\", l0.\"title\", l0.\"bcp47\", l0.\"doc_language\", l0.\"language_id\" FROM \"language_translations\" AS l0 WHERE (websearch_to_tsquery($1::text::regconfig, $2)\n::text @@ to_tsvector(lang_to_regconfig(doc_language),coalesce(title, ''))\n::text OR SIMILARITY($3, title)\n::float > 0\n) ORDER BY NULLIF(ts_rank(to_tsvector(title), websearch_to_tsquery($4::text::regconfig, $5)\n::text), 0)::float, SIMILARITY($6, title)\n::float DESC NULLS LAST\n\n    hint: No function matches the given name and argument types. You might need to add explicit type casts."

gotta drop off for a meeting here

ZachDaniel•2y ago

Yeah, the problem is that its a tsquery I'll have a fix for you soon 🙂 The easiest fix for this at the moment is creating a wrapper type for tsquery called AshPostgres.Tsquery So then what you'd do is make your argument argument :query, AshPostgres.Tsquery and calculate :query, AshPostgres.Tsquery, ... A new release of ash is out that has some needed fixes for this, and I'll push up the postgres side to ash_postgres main When you get a chance, try out ash_postgres main and latest ash version and LMK if that resolves the issue 🙂 okay, released

gdub01OP•2y ago

Wow thanks man. That was fast 🙂 I just tried it and looks like I'm stilling hitting.. somthing. In mix:

  "ash_postgres": {:git, "https://github.com/ash-project/ash_postgres.git", "b35f8c715bb4bc8cdc690b58e7ad532d69e66ec8", []},
  "ash": {:hex, :ash, "2.14.18" ...},

  "ash_postgres": {:git, "https://github.com/ash-project/ash_postgres.git", "b35f8c715bb4bc8cdc690b58e7ad532d69e66ec8", []},
  "ash": {:hex, :ash, "2.14.18" ...},

I updated to AshPostgres.Tsquery: Rank title:

    calculate :rank_title,
              AshPostgres.Tsquery,
              expr(
                fragment(
                  "NULLIF(ts_rank(to_tsvector(title), ?), 0)",
                  query(doc_language: arg(:doc_language), search_term: arg(:search_term))
                )
              ) do
      argument :doc_language, :string, allow_nil?: false
      argument :search_term, :string, allow_nil?: false
    end

    calculate :rank_title,
              AshPostgres.Tsquery,
              expr(
                fragment(
                  "NULLIF(ts_rank(to_tsvector(title), ?), 0)",
                  query(doc_language: arg(:doc_language), search_term: arg(:search_term))
                )
              ) do
      argument :doc_language, :string, allow_nil?: false
      argument :search_term, :string, allow_nil?: false
    end

Query:

    calculate :query,
              AshPostgres.Tsquery,
              expr(
                fragment(
                  """
                  websearch_to_tsquery(?::text::regconfig, ?)
                  """,
                  ^arg(:doc_language),
                  ^arg(:search_term)
                )
              ) do
      argument :doc_language, :string, allow_nil?: false
      argument :search_term, :string, allow_nil?: false
    end

    calculate :query,
              AshPostgres.Tsquery,
              expr(
                fragment(
                  """
                  websearch_to_tsquery(?::text::regconfig, ?)
                  """,
                  ^arg(:doc_language),
                  ^arg(:search_term)
                )
              ) do
      argument :doc_language, :string, allow_nil?: false
      argument :search_term, :string, allow_nil?: false
    end

and getting:

"** (Postgrex.Error) ERROR 42883 (undefined_function) operator does not exist: tsquery @@ text\n\n    query: SELECT l0.\"id\", l0.\"title\", l0.\"bcp47\", l0.\"doc_language\", l0.\"language_id\" FROM \"language_translations\" AS l0 WHERE (websearch_to_tsquery($1::text::regconfig, $2)\n::tsquery @@ to_tsvector(lang_to_regconfig(doc_language),coalesce(title, ''))\n::text OR SIMILARITY($3, title)\n::float > 0\n) ORDER BY NULLIF(ts_rank(to_tsvector(title), websearch_to_tsquery($4::text::regconfig, $5)\n::tsquery), 0)::tsquery, SIMILARITY($6, title)\n::float DESC NULLS LAST\n\n    hint: No operator matches the given name and argument types. You might need to add explicit type casts."

"** (Postgrex.Error) ERROR 42883 (undefined_function) operator does not exist: tsquery @@ text\n\n    query: SELECT l0.\"id\", l0.\"title\", l0.\"bcp47\", l0.\"doc_language\", l0.\"language_id\" FROM \"language_translations\" AS l0 WHERE (websearch_to_tsquery($1::text::regconfig, $2)\n::tsquery @@ to_tsvector(lang_to_regconfig(doc_language),coalesce(title, ''))\n::text OR SIMILARITY($3, title)\n::float > 0\n) ORDER BY NULLIF(ts_rank(to_tsvector(title), websearch_to_tsquery($4::text::regconfig, $5)\n::tsquery), 0)::tsquery, SIMILARITY($6, title)\n::float DESC NULLS LAST\n\n    hint: No operator matches the given name and argument types. You might need to add explicit type casts."

ZachDaniel•2y ago

two things the :rank_title doesn't need to be updated to Tsquery that returns a float or something, IIRC Where is the @@ coming from? Feels like there is a part of the calculation failing that I can't see currently

gdub01OP•2y ago

Okay I updated rank title to float and it's still doing the same. The @@ is from postgres. WHERE query @@ document OR similarity > 0 is the vanilla way to do it. I've seen it elsehwere like this: WHERE to_tsvector('english', query) @@ websearch_to_tsquery('english', 'natural loofah')

ZachDaniel•2y ago

🤔 So you don't have @@ in one of your calculations?

gdub01OP•2y ago

correct

      filter expr(
               fragment(
                 """
                 ? @@ ? OR ? > 0
                 """,
                 query(doc_language: arg(:doc_language), search_term: arg(:search_term)),
                 document,
                 similarity(search_term: arg(:search_term))
               )
             )

      filter expr(
               fragment(
                 """
                 ? @@ ? OR ? > 0
                 """,
                 query(doc_language: arg(:doc_language), search_term: arg(:search_term)),
                 document,
                 similarity(search_term: arg(:search_term))
               )
             )

just the filter expression

ZachDaniel•2y ago

🤔 oh I'm pretty sure thats where its coming from two things

gdub01OP•2y ago

    read :search do
      argument :search_term, :string do
        allow_nil? false
      end

      argument :doc_language, :string do
        allow_nil? false
      end

      filter expr(
               fragment(
                 """
                 ? @@ ? OR ? > 0
                 """,
                 query(doc_language: arg(:doc_language), search_term: arg(:search_term)),
                 document,
                 similarity(search_term: arg(:search_term))
               )
             )

      prepare(
        build(
          sort: [
            rank_title:
              {:asc, %{doc_language: arg(:doc_language), search_term: arg(:search_term)}},
            similarity: {:desc_nils_last, %{search_term: arg(:search_term)}}
          ]
        )
      )
    end

    read :search do
      argument :search_term, :string do
        allow_nil? false
      end

      argument :doc_language, :string do
        allow_nil? false
      end

      filter expr(
               fragment(
                 """
                 ? @@ ? OR ? > 0
                 """,
                 query(doc_language: arg(:doc_language), search_term: arg(:search_term)),
                 document,
                 similarity(search_term: arg(:search_term))
               )
             )

      prepare(
        build(
          sort: [
            rank_title:
              {:asc, %{doc_language: arg(:doc_language), search_term: arg(:search_term)}},
            similarity: {:desc_nils_last, %{search_term: arg(:search_term)}}
          ]
        )
      )
    end

that's the whole read action.

ZachDaniel•2y ago

      filter expr(
               fragment(
                 """
                 (? @@ ? OR ? > 0)
                 """,
                 query(doc_language: arg(:doc_language), search_term: arg(:search_term)),
                 document,
                 similarity(search_term: arg(:search_term))
               )
             )

      filter expr(
               fragment(
                 """
                 (? @@ ? OR ? > 0)
                 """,
                 query(doc_language: arg(:doc_language), search_term: arg(:search_term)),
                 document,
                 similarity(search_term: arg(:search_term))
               )
             )

Best practice to surround fragments in parenthesis we can't do it automagically for various reasons (but we might someday, when we can make sure it works for all variations of expressions) second thing is what is document? IS that the string attribute on the resource? tsvector @@ tsquery is the type signature for that operator

gdub01OP•2y ago

Oh shoot ya good point

    calculate :document,
              :string,
              expr(
                fragment("""
                (to_tsvector(lang_to_regconfig(doc_language),coalesce(title, '')))
                """)
              )

    calculate :document,
              :string,
              expr(
                fragment("""
                (to_tsvector(lang_to_regconfig(doc_language),coalesce(title, '')))
                """)
              )

so that shouldn't be a string then maybe

ZachDaniel•2y ago

ah, okay 😆 looks like you'd also want a tsvector type along the same vein 😆

gdub01OP•2y ago

Ah nooo I missed that earlier man so sorry

ZachDaniel•2y ago

no worries not your fault There should actually be some kind of escape hatch here to avoid needing to define type modules to map to underlying types its easy to do, but not super easy to know how or that you need to do it

defmodule AshPostgres.Tsvector do
  use Ash.Type.NewType, subtype_of: :term

  @impl true
  def storage_type(_), do: :tsvector
end

defmodule AshPostgres.Tsvector do
  use Ash.Type.NewType, subtype_of: :term

  @impl true
  def storage_type(_), do: :tsvector
end

I've just pushed it up to ash_postgres main give that a shot and see how it works for you 🙂

gdub01OP•2y ago

wow man lightning. 🙂 ty okay it works!!!! 💯 thanks so much man. that's amazing.

ZachDaniel•2y ago

🥳

Gaming

Programming

Fragment with multiple 'from' items.

Did you find this page helpful?