Help on creating a any boolean aggregation

Elixir Framework Forums Ash Forum
,
1

Hi there, basically I’m trying to create an aggregation that would be similar to this:

Enum.any?(values, fn x -> x end)

The value I’m trying to aggregate is called :fix_and_flip? which is a boolean calculation inside a resource that I get from a has_many called :owner_records

I think I can achieve that with the first aggregate:

    first :fix_and_flip?, :owner_records, :fix_and_flip? do
      filter [fix_and_flip?: true]
    end

But when I load this aggregate, I get the following error:

{:error,
 %Ash.Error.Unknown{
   errors: [
     %Ash.Error.Unknown.UnknownError{
       error: "** (Ecto.SubQueryError) the following exception happened when compiling a subquery.\n\n    ** (Ecto.QueryError) /run/host/home/sezdocs/projects/rebuilt/platform/pacman/deps/ash_postgres/lib/aggregate.ex:1049: field `property_id` in `select` does not exist in schema Pacman.Raw.Record in query:\n    \n    from r0 in Pacman.Raw.Record,\n      as: 0,\n      left_join: r1 in Pacman.Raw.Record,\n      on: type(\n      as(1).external_id,\n      {:parameterized, Ash.Type.StringWrapper.EctoType, allow_empty?: false, trim?: true,\n       max_length: 255}\n    ) ==\n      type(\n        as(0).external_id,\n        {:parameterized, Ash.Type.StringWrapper.EctoType, allow_empty?: false, trim?: true,\n         max_length: 255}\n      ) and\n      fragment(\n        \"(? %> ?)\",\n        type(\n          as(1).first_grantor_full_name,\n          {:parameterized, Ash.Type.StringWrapper.EctoType, allow_empty?: false, trim?: true,\n           max_length: 255}\n        ),\n        type(\n          as(0).first_grantee_full_name,\n          {:parameterized, Ash.Type.StringWrapper.EctoType, allow_empty?: false, trim?: true,\n           max_length: 255}\n        )\n      ) and\n      not (is_nil(\n             fragment(\n               \"ash_elixir_or(?, ?)\",\n               type(as(1).recording_date, {:parameterized, Ash.Type.Date.EctoType, []}),\n               type(as(1).instrument_date, {:parameterized, Ash.Type.Date.EctoType, []})\n             )\n           ) == ^true) and\n      fragment(\n        \"ash_elixir_or(?, ?)\",\n        type(as(1).recording_date, {:parameterized, Ash.Type.Date.EctoType, []}),\n        type(as(1).instrument_date, {:parameterized, Ash.Type.Date.EctoType, []})\n      ) >=\n        fragment(\n          \"ash_elixir_or(?, ?)\",\n          type(as(0).recording_date, {:parameterized, Ash.Type.Date.EctoType, []}),\n          type(as(0).instrument_date, {:parameterized, Ash.Type.Date.EctoType, []})\n        ) and not type(as(1).deleted?, {:parameterized, Ash.Type.Boolean.EctoType, []}),\n      where: fragment(\n      \"(? %> ?)\",\n      type(\n        as(0).first_grantee_full_name,\n        {:parameterized, Ash.Type.StringWrapper.EctoType, allow_empty?: false, trim?: true,\n         max_length: 255}\n      ),\n      type(\n        fragment(\n          \"jsonb_extract_path_text(?::jsonb,?::text)\",\n          type(\n            parent_as(0).first_owner,\n            {:parameterized, Pacman.Markets.Property.Owner.EctoType, []}\n          ),\n          ^\"full_name\"\n        ),\n        {:parameterized, Ash.Type.StringWrapper.EctoType, allow_empty?: false, trim?: true,\n         max_length: 255}\n      )\n    ),\n      where: type(\n      fragment(\n        \"coalesce(? between ? and ?, false)\",\n        fragment(\n          \"ash_elixir_or(?, ?)\",\n          type(as(1).recording_date, {:parameterized, Ash.Type.Date.EctoType, []}),\n          type(as(1).instrument_date, {:parameterized, Ash.Type.Date.EctoType, []})\n        ) -\n          fragment(\n            \"ash_elixir_or(?, ?)\",\n            type(as(0).recording_date, {:parameterized, Ash.Type.Date.EctoType, []}),\n            type(as(0).instrument_date, {:parameterized, Ash.Type.Date.EctoType, []})\n          ),\n        ^90,\n        ^270\n      ),\n      {:parameterized, Ash.Type.Boolean.EctoType, []}\n    ) == type(^true, {:parameterized, Ash.Type.Boolean.EctoType, []}),\n      where: parent_as(0).id == as(0).property_id,\n      group_by: [r0.property_id],\n      select: %{\n      fix_and_flip?:\n        type(\n          fragment(\n            \"(?)[1]\",\n            fragment(\n              \"array_agg(?)\",\n              type(\n                fragment(\n                  \"coalesce(? between ? and ?, false)\",\n                  fragment(\n                    \"ash_elixir_or(?, ?)\",\n                    type(as(1).recording_date, {:parameterized, Ash.Type.Date.EctoType, []}),\n                    type(as(1).instrument_date, {:parameterized, Ash.Type.Date.EctoType, []})\n                  ) -\n                    fragment(\n                      \"ash_elixir_or(?, ?)\",\n                      type(as(0).recording_date, {:parameterized, Ash.Type.Date.EctoType, []}),\n                " <> ...,
       field: nil,
       changeset: nil,
       query: nil,
       error_context: [],
       vars: [],
       path: [],
       stacktrace: #Stacktrace<>,
       class: :unknown
     }
   ],
   stacktraces?: true,
   changeset: nil,
   query: #Ash.Query<
     resource: Pacman.Markets.Property,
     load: [owner_records: []],
     aggregates: %{
       fix_and_flip?: #first<
         owner_records.fix_and_flip? from #Ash.Query<resource: Pacman.Raw.Record,
          filter: #Ash.Filter<fix_and_flip? == true>>
       >
     },
     errors: [
       %Ash.Error.Unknown.UnknownError{
         error: "** (Ecto.SubQueryError) the following exception happened when compiling a subquery.\n\n    ** (Ecto.QueryError) /run/host/home/sezdocs/projects/rebuilt/platform/pacman/deps/ash_postgres/lib/aggregate.ex:1049: field `property_id` in `select` does not exist in schema Pacman.Raw.Record in query:\n    \n    from r0 in Pacman.Raw.Record,\n      as: 0,\n      left_join: r1 in Pacman.Raw.Record,\n      on: type(\n      as(1).external_id,\n      {:parameterized, Ash.Type.StringWrapper.EctoType, allow_empty?: false, trim?: true,\n       max_length: 255}\n    ) ==\n      type(\n        as(0).external_id,\n        {:parameterized, Ash.Type.StringWrapper.EctoType, allow_empty?: false, trim?: true,\n         max_length: 255}\n      ) and\n      fragment(\n        \"(? %> ?)\",\n        type(\n          as(1).first_grantor_full_name,\n          {:parameterized, Ash.Type.StringWrapper.EctoType, allow_empty?: false, trim?: true,\n           max_length: 255}\n        ),\n        type(\n          as(0).first_grantee_full_name,\n          {:parameterized, Ash.Type.StringWrapper.EctoType, allow_empty?: false, trim?: true,\n           max_length: 255}\n        )\n      ) and\n      not (is_nil(\n             fragment(\n               \"ash_elixir_or(?, ?)\",\n               type(as(1).recording_date, {:parameterized, Ash.Type.Date.EctoType, []}),\n               type(as(1).instrument_date, {:parameterized, Ash.Type.Date.EctoType, []})\n             )\n           ) == ^true) and\n      fragment(\n        \"ash_elixir_or(?, ?)\",\n        type(as(1).recording_date, {:parameterized, Ash.Type.Date.EctoType, []}),\n        type(as(1).instrument_date, {:parameterized, Ash.Type.Date.EctoType, []})\n      ) >=\n        fragment(\n          \"ash_elixir_or(?, ?)\",\n          type(as(0).recording_date, {:parameterized, Ash.Type.Date.EctoType, []}),\n          type(as(0).instrument_date, {:parameterized, Ash.Type.Date.EctoType, []})\n        ) and not type(as(1).deleted?, {:parameterized, Ash.Type.Boolean.EctoType, []}),\n      where: fragment(\n      \"(? %> ?)\",\n      type(\n        as(0).first_grantee_full_name,\n        {:parameterized, Ash.Type.StringWrapper.EctoType, allow_empty?: false, trim?: true,\n         max_length: 255}\n      ),\n      type(\n        fragment(\n          \"jsonb_extract_path_text(?::jsonb,?::text)\",\n          type(\n            parent_as(0).first_owner,\n            {:parameterized, Pacman.Markets.Property.Owner.EctoType, []}\n          ),\n          ^\"full_name\"\n        ),\n        {:parameterized, Ash.Type.StringWrapper.EctoType, allow_empty?: false, trim?: true,\n         max_length: 255}\n      )\n    ),\n      where: type(\n      fragment(\n        \"coalesce(? between ? and ?, false)\",\n        fragment(\n          \"ash_elixir_or(?, ?)\",\n          type(as(1).recording_date, {:parameterized, Ash.Type.Date.EctoType, []}),\n          type(as(1).instrument_date, {:parameterized, Ash.Type.Date.EctoType, []})\n        ) -\n          fragment(\n            \"ash_elixir_or(?, ?)\",\n            type(as(0).recording_date, {:parameterized, Ash.Type.Date.EctoType, []}),\n            type(as(0).instrument_date, {:parameterized, Ash.Type.Date.EctoType, []})\n          ),\n        ^90,\n        ^270\n      ),\n      {:parameterized, Ash.Type.Boolean.EctoType, []}\n    ) == type(^true, {:parameterized, Ash.Type.Boolean.EctoType, []}),\n      where: parent_as(0).id == as(0).property_id,\n      group_by: [r0.property_id],\n      select: %{\n      fix_and_flip?:\n        type(\n          fragment(\n            \"(?)[1]\",\n            fragment(\n              \"array_agg(?)\",\n              type(\n                fragment(\n                  \"coalesce(? between ? and ?, false)\",\n                  fragment(\n                    \"ash_elixir_or(?, ?)\",\n                    type(as(1).recording_date, {:parameterized, Ash.Type.Date.EctoType, []}),\n                    type(as(1).instrument_date, {:parameterized, Ash.Type.Date.EctoType, []})\n                  ) -\n                    fragment(\n                      \"ash_elixir_or(?, ?)\",\n                      type(as(0).recording_date, {:parameterized, Ash.Type.Date.EctoType, []}),\n                " <> ...,
         field: nil,
         changeset: nil,
         query: nil,
         error_context: [],
         vars: [],
         path: [],
         stacktrace: #Stacktrace<>,
         class: :unknown
       }
     ]
   >,
   error_context: [nil],
   vars: [],
   path: [],
   stacktrace: #Stacktrace<>,
   class: :unknown
 }}

Just for completeness, here is the has_many block:

    has_many :owner_records, Record do
      no_attributes? true

      read_action :valid

      filter expr(
            fragment("(? %> ?)", first_grantee_full_name, parent(first_owner[:full_name]))
            )
    end

And here is the calculation I’m using in the joined resource:

    calculate :fix_and_flip?,
              :boolean,
              expr(
                fragment(
                  "coalesce(? between ? and ?, false)",
                  (sell_record.recording_date || sell_record.instrument_date) -
                    (recording_date || instrument_date),
                  90,
                  270
                )
              ) do
      allow_nil? false
    end
2

Here are some other alternatives I’ve tried so far:

Adding a custom calculation:

calculate :fix_and_flip?, :boolean, FixAndFlip

...

defmodule FixAndFlip do
  use Ash.Calculation

  @impl true
  def load(_query, _opts, _context), do: [:owner_records]

  @impl true
  def calculate(properties, _opts, _) do
    Enum.map(properties, fn property ->
      Enum.any?(property.owner_records, fn record -> record.fix_and_flip? end)
    end)
  end
end

This one works, it will load correctly, but I can’t use inside a Ash.Query.filter because expression/0 is not implemented and I’m not sure how to implement it.

Adding a calculate with an aggregation inline:

calculate :fix_and_flip?, :boolean, expr(first(owner_records, query: [filter: expr(fix_and_flip? == true)]))

This will fail with the following error:

** (KeyError) key :type not found in: nil

If you are using the dot syntax, such as map.field, make sure the left-hand side of the dot is a map
    (ash_postgres 1.3.60) lib/aggregate.ex:634: AshPostgres.Aggregate.array_type?/2
    (ash_postgres 1.3.60) lib/aggregate.ex:609: AshPostgres.Aggregate.can_group_kind?/2
    (ash_postgres 1.3.60) lib/aggregate.ex:588: AshPostgres.Aggregate.can_group?/2
    (elixir 1.15.7) lib/enum.ex:2333: anonymous fn/3 in Enum.split_with/2
    (elixir 1.15.7) lib/enum.ex:2510: Enum."-split_with/2-lists^foldl/2-0-"/3
    (elixir 1.15.7) lib/enum.ex:2510: Enum.split_with/2
    (ash_postgres 1.3.60) lib/aggregate.ex:59: anonymous fn/2 in AshPostgres.Aggregate.add_aggregates/6
    (elixir 1.15.7) lib/enum.ex:1260: anonymous fn/3 in Enum.flat_map/2
3

You can use the exists aggregate type for that.

aggregates do
    exists :fix_and_flip?, :owner_records do
      filter expr(fix_and_flip? == true)
    end
end
4

Thanks a bunch, that worked great!

1 Like
5

Actual @zachdaniel that aggregate doesn’t seem to work as expected.

I just noticed that it still shows true even though all the records inside :newer_records have fix_and_flip? value set to false.

I’m testing it using both ash and ash_postgres from master

6

Btw @zachdaniel the problem here is pretty similar to the other topic about count aggregates.

Here is the left join part:

SELECT 1 AS "result"
FROM "raw"."records" AS sr0
WHERE ((sr0."first_grantee_full_name"::text %> jsonb_extract_path_text(p0."first_owner"::jsonb::jsonb,$1::text)::text))

As you can see, it is missing the filters from the :valid read action, with it the query would be:

SELECT 1 AS "result"
FROM "raw"."records" AS sr0
WHERE ((sr0."first_grantee_full_name"::text %> jsonb_extract_path_text(p0."first_owner"::jsonb::jsonb,$1::text)::text))
  AND (NOT ((ash_elixir_or(sr0."recording_date"::date, sr0."instrument_date"::date) IS NULL) = true::boolean))
  AND (ash_elixir_or(sr0."recording_date"::date, sr0."instrument_date"::date) >= ('2023-11-16 23:46:11.790252Z'::timestamp + (2::bigint * -1::decimal::numeric * interval '1 year')))
  AND (NOT (sr0."deleted?"::boolean))

note that this is still incorrect, my aggregates has a filter filter [fix_and_flip?: true] and as you can see, that filter is not part of the where clause, so it still returns wrong results because of it.

7

:thinking: this is pretty strange. We have tests showing that the filter statement on the aggregate works properly. :thinking: I might need to see the whole query output. Can you also try filter expr(fix_and_flip? == true) I highly doubt that is the issue, as even in our tests we are using a keyword filter not a expression. Mind showing me your latest aggregate definition as well? Just for a sanity check.

8

Yeah, I’m not able to reproduce this behavior currently, but you aren’t the only one reporting it. What postgres version are you on?

Similar or same issue: `exists` aggregation ignores `filter` option · Issue #173 · ash-project/ash_postgres · GitHub

It is also possible that this was fixed in main recently? Otherwise, I’m not sure.

9

Nevermind, I figured it out. It was a bug, fixed in main.

1 Like