Terry Lee Posts - Answer Overflow

Terry Lee

•Created by Terry Lee on 7/15/2023 in #support

Full debug SQL

Hi, during development, the sql statement is printed out by the AshPostgres.DataLayer.run_aggregate_query/3, at: lib/data_layer.ex:738 A long enough sql would be truncated. How do I get the full sql without truncation? Thanks!

2 replies

AEAsh Elixir

•Created by Terry Lee on 7/10/2023 in #support

How to put the same value at multiple places in a fragment?

Say I have a custom postgres function and Is it possible to do the following fragment fragment("SELECT my_custom_function(1/<rate>, 2/<rate>, 3*<rate>) FROM lookup_values WHERE id=<id>", rate, id)? Today, I will need to do fragment("SELECT my_custom_function(1/?, 2/?, 3*?) FROM lookup_values WHERE id=?", rate, rate, rate, id)

3 replies

AEAsh Elixir

•Created by Terry Lee on 7/9/2023 in #support

How do you sort by substring?

Current in my resource

actions do
    defaults [:read]

    read :list do
      prepare build(sort: [sale_reference: :desc])
      filter expr(is_nil(project))
    end
  end

actions do
    defaults [:read]

    read :list do
      prepare build(sort: [sale_reference: :desc])
      filter expr(is_nil(project))
    end
  end

I would like to sort by substring -> select * from resources order by substring(sale_reference from 3) desc

4 replies

AEAsh Elixir

•Created by Terry Lee on 5/24/2023 in #support

JsonB sorting within Resource.actions

I have a Resource with a jsonb column called metadata How do I default sort by `metadata ->> 'CreatedDate'?

actions do
    read :read do
      prepare build(sort: [expr(fragment("metadata->>'CreatedTime' DESC"))])   # <-- How do I do this?!
      filter expr(isproject == true)
    end
  end

actions do
    read :read do
      prepare build(sort: [expr(fragment("metadata->>'CreatedTime' DESC"))])   # <-- How do I do this?!
      filter expr(isproject == true)
    end
  end

16 replies

AEAsh Elixir

•Created by Terry Lee on 5/21/2023 in #support

JSONB <=> Map fails on Array

I have a column of type jsonb. Let's call this column metadata In my resource, I define the attributes as

attributes do
    integer_primary_key :id
    attribute :metadata, :map
end

attributes do
    integer_primary_key :id
    attribute :metadata, :map
end

If my metadata is a json element, the data is returned properly. However, if the metadata is an array of json elements, the Ash Query fails. Sample error response

[debug] QUERY OK source="maptest" db=205.5ms queue=0.1ms idle=942.4ms
SELECT m0."id", m0."metadata" FROM "maptest" AS m0 WHERE (m0."id"::bigint = $1::bigint) [1]
↳ AshPostgres.DataLayer.run_query/2, at: lib/data_layer.ex:628
** (ArgumentError) cannot load `[%{"Amount" => 925.93, "Description" => "PROPOSED INTERIOR DESIGN & RENOVATION", "DescriptionLineDetail" => nil, "DetailType" => "SalesItemLineDetail", "DiscountLineDetail" => nil, "Id" => "1", "LineNum" => "1", "SalesItemLineDetail" => %{"ClassRef" => nil, "Description" => nil, "ItemAccountRef" => %{"name" => "Sales - Renovation", "value" => "6"}, "ItemRef" => %{"name" => "Sales - Renovation", "value" => "6"}, "Qty" => 1.0, "ServiceDate" => "2023-02-26T00:00:00.000000Z", "TaxCodeRef" => %{"value" => "27"}, "UnitPrice" => 925.93}, "SubTotalLineDetail" => nil}, %{"Amount" => 925.93, "Description" => nil, "DescriptionLineDetail" => nil, "DetailType" => "SubTotalLineDetail", "DiscountLineDetail" => nil, "Id" => nil, "LineNum" => nil, "SalesItemLineDetail" => nil, "SubTotalLineDetail" => %{}}]` as type #Ash.Type.Map.EctoType<[]> for field :metadata in #Vosmos.Sale.Resources.MapTest<__meta__: #Ecto.Schema.Metadata<:loaded, "maptest">, id: nil, metadata: nil, aggregates: %{}, calculations: %{}, __order__: nil, ...>
    (ecto 3.10.1) lib/ecto/repo/queryable.ex:419: Ecto.Repo.Queryable.struct_load!/6
    (ecto 3.10.1) lib/ecto/repo/queryable.ex:243: anonymous fn/5 in Ecto.Repo.Queryable.preprocessor/3
    (elixir 1.14.0) lib/enum.ex:1658: Enum."-map/2-lists^map/1-0-"/2.....

[debug] QUERY OK source="maptest" db=205.5ms queue=0.1ms idle=942.4ms
SELECT m0."id", m0."metadata" FROM "maptest" AS m0 WHERE (m0."id"::bigint = $1::bigint) [1]
↳ AshPostgres.DataLayer.run_query/2, at: lib/data_layer.ex:628
** (ArgumentError) cannot load `[%{"Amount" => 925.93, "Description" => "PROPOSED INTERIOR DESIGN & RENOVATION", "DescriptionLineDetail" => nil, "DetailType" => "SalesItemLineDetail", "DiscountLineDetail" => nil, "Id" => "1", "LineNum" => "1", "SalesItemLineDetail" => %{"ClassRef" => nil, "Description" => nil, "ItemAccountRef" => %{"name" => "Sales - Renovation", "value" => "6"}, "ItemRef" => %{"name" => "Sales - Renovation", "value" => "6"}, "Qty" => 1.0, "ServiceDate" => "2023-02-26T00:00:00.000000Z", "TaxCodeRef" => %{"value" => "27"}, "UnitPrice" => 925.93}, "SubTotalLineDetail" => nil}, %{"Amount" => 925.93, "Description" => nil, "DescriptionLineDetail" => nil, "DetailType" => "SubTotalLineDetail", "DiscountLineDetail" => nil, "Id" => nil, "LineNum" => nil, "SalesItemLineDetail" => nil, "SubTotalLineDetail" => %{}}]` as type #Ash.Type.Map.EctoType<[]> for field :metadata in #Vosmos.Sale.Resources.MapTest<__meta__: #Ecto.Schema.Metadata<:loaded, "maptest">, id: nil, metadata: nil, aggregates: %{}, calculations: %{}, __order__: nil, ...>
    (ecto 3.10.1) lib/ecto/repo/queryable.ex:419: Ecto.Repo.Queryable.struct_load!/6
    (ecto 3.10.1) lib/ecto/repo/queryable.ex:243: anonymous fn/5 in Ecto.Repo.Queryable.preprocessor/3
    (elixir 1.14.0) lib/enum.ex:1658: Enum."-map/2-lists^map/1-0-"/2.....

2 replies

AEAsh Elixir

•Created by Terry Lee on 5/15/2023 in #support

Skip Migration for a Resource

Question: How do I skip generation of migration for a particular resource? Context, I have an existing ongoing project that I want to port over to STAPLE. The project 1. has existing tables and data in production. The migrations are separately maintained using LIquibase 2. has views that combines data from multiple tables from multiple schemas. I'd like to define the tables and views as Ash Resources. Firstly, can do? I'm assuming that i can just put the name of the view into the table element in postgres. Secondly, hence, how do I skip migration generation for these resources entirely? Though I would like to generate new tables Third question, do I use

postgres do
  table "myschema.tickets"
end

postgres do
  table "myschema.tickets"
end

postgres do
  table "tickets"
  schema "myschema"
end

postgres do
  table "tickets"
  schema "myschema"
end

I'm gonna give the above a go anyways. Just wanna ask for pointers. And I couldn't find where I can skip migration of entire resource. Cheers!

4 replies

AEAsh Elixir

•Created by Terry Lee on 5/8/2023 in #support

Self-referencing a Resource

Question: How do I self-reference a Resource? Detail Description: I have a Person table. I want to describe that he is related to another Person(s). I don't need to know relationship type, except that they are related/know each other Technical Description: Person A :has_many Persons(s) ?

2 replies

Gaming

Programming