96 replies

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

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.

Ash Framework•3y ago•

96 replies

gdub01

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

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.

Fragment with multiple 'from' items.

Fragment with multiple 'from' items.

Similar Threads

Similar Threads

Similar Threads