gdub01 Comments - Answer Overflow

    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, '')))
                """)
              )

97 replies

AEAsh Elixir

•Created by gdub01 on 9/20/2023 in #support

Fragment with multiple 'from' items.

Oh shoot ya good point

97 replies

AEAsh Elixir

•Created by gdub01 on 9/20/2023 in #support

Fragment with multiple 'from' items.

    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.

97 replies

AEAsh Elixir

•Created by gdub01 on 9/20/2023 in #support

Fragment with multiple 'from' items.

just the filter expression

97 replies

AEAsh Elixir

•Created by gdub01 on 9/20/2023 in #support

Fragment with multiple 'from' items.

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

97 replies

AEAsh Elixir

•Created by gdub01 on 9/20/2023 in #support

Fragment with multiple 'from' items.

correct

97 replies

AEAsh Elixir

•Created by gdub01 on 9/20/2023 in #support

Fragment with multiple 'from' items.

I've seen it elsehwere like this: WHERE to_tsvector('english', query) @@ websearch_to_tsquery('english', 'natural loofah')

97 replies

AEAsh Elixir

•Created by gdub01 on 9/20/2023 in #support

Fragment with multiple 'from' items.

The @@ is from postgres. WHERE query @@ document OR similarity > 0 is the vanilla way to do it.

97 replies

AEAsh Elixir

•Created by gdub01 on 9/20/2023 in #support

Fragment with multiple 'from' items.

Okay I updated rank title to float and it's still doing the same.

97 replies

AEAsh Elixir

•Created by gdub01 on 9/20/2023 in #support

Fragment with multiple 'from' items.

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."

97 replies

AEAsh Elixir

•Created by gdub01 on 9/20/2023 in #support

Fragment with multiple 'from' items.

Wow thanks man. That was fast 🙂

97 replies

AEAsh Elixir

•Created by gdub01 on 9/20/2023 in #support

Fragment with multiple 'from' items.

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

97 replies

AEAsh Elixir

•Created by gdub01 on 9/20/2023 in #support

Fragment with multiple 'from' items.

97 replies

AEAsh Elixir

•Created by gdub01 on 9/20/2023 in #support

Fragment with multiple 'from' items.

so I know ts_rank exists.

97 replies

AEAsh Elixir

•Created by gdub01 on 9/20/2023 in #support

Fragment with multiple 'from' items.

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

97 replies

Gaming

Programming