gdub01 Posts - Answer Overflow

gdub01

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

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.

97 replies

AEAsh Elixir

•Created by gdub01 on 5/10/2023 in #support

Association not working. Is 'property' a reserved name?

I am trying to associate one resource with another. This works well for most resources I have. However there's this one resource called 'Property' that I can' get to associate with any other resource. If you see this code after submitting a form:

form #=> #AshPhoenix.Form<
  resource: Clients.Goal,
  action: :create,
  type: :create,
  params: %{
    "agency_id" => "c0863320-cd12-4334-9e19-de769f9d4add",
    "client_id" => "500ad228-3f76-4754-a081-f7c5659fce3d",
    "property_id" => "59131003-cefd-48af-8fe3-0bc16cc6d7c8",
    "title" => "fqds"
  },
  source: #Ash.Changeset<
    api: Clients,
    action_type: :create,
    action: :create,
    attributes: %{
      agency_id: "c0863320-cd12-4334-9e19-de769f9d4add",
      client_id: "500ad228-3f76-4754-a081-f7c5659fce3d",
      id: "4063e291-bae0-4f79-b550-e8227f906d5c",
      title: "fqds"
    },

form #=> #AshPhoenix.Form<
  resource: Clients.Goal,
  action: :create,
  type: :create,
  params: %{
    "agency_id" => "c0863320-cd12-4334-9e19-de769f9d4add",
    "client_id" => "500ad228-3f76-4754-a081-f7c5659fce3d",
    "property_id" => "59131003-cefd-48af-8fe3-0bc16cc6d7c8",
    "title" => "fqds"
  },
  source: #Ash.Changeset<
    api: Clients,
    action_type: :create,
    action: :create,
    attributes: %{
      agency_id: "c0863320-cd12-4334-9e19-de769f9d4add",
      client_id: "500ad228-3f76-4754-a081-f7c5659fce3d",
      id: "4063e291-bae0-4f79-b550-e8227f906d5c",
      title: "fqds"
    },

You'll notice I'm submitting property_id in params. However it is not in the attributes section from the source. Not sure why that is. I have:

    create :create do
      primary? true
      argument(:client_id, :uuid)
      argument(:agency_id, :uuid)
      argument(:property_id, :uuid)

      change(manage_relationship(:agency_id, :agency, type: :append))
      change(manage_relationship(:client_id, :client, type: :append))
      change(manage_relationship(:property_id, :property, type: :append))
    end

    create :create do
      primary? true
      argument(:client_id, :uuid)
      argument(:agency_id, :uuid)
      argument(:property_id, :uuid)

      change(manage_relationship(:agency_id, :agency, type: :append))
      change(manage_relationship(:client_id, :client, type: :append))
      change(manage_relationship(:property_id, :property, type: :append))
    end

and

  relationships do
    belongs_to :agency, Accounts.Agency do
      allow_nil?(false)
      api(Accounts)
    end

    belongs_to :client, Accounts.Client do
      allow_nil?(false)
      api(Accounts)
    end

    belongs_to(:property, Clients.Property)
  end

  relationships do
    belongs_to :agency, Accounts.Agency do
      allow_nil?(false)
      api(Accounts)
    end

    belongs_to :client, Accounts.Client do
      allow_nil?(false)
      api(Accounts)
    end

    belongs_to(:property, Clients.Property)
  end

So I can associate accounts and clients, but not properties using the same-ish code. Just wondering if I may need to call my Property resource something else? The form submit shows there's errors, but does't say what the actual errors are. So wondering if it's kind of an edge case?

7 replies

Gaming

Programming