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:
I think I would write a fragment like:
on the read action.
However for the
I had tried:
but get this:
which makes sense but I"m wondering how to make them work together.
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 LASTSELECT
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 LASTI 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
FROMFROM 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.
