Only one select expression is allowed in query error in relationship

sezaru · January 3, 2025, 2:29pm

I have a read action action in my resource that will load the following relationship:

    has_one :market, Markets.Market do
      no_attributes? true
      allow_nil? true

      filter expr(
               parent(normalized_county) == regions.county and
                 parent(normalized_state) == regions.state
             )
    end

For some reason, I get the following error when the load is ran:

     ** (Ash.Error.Unknown)
     Bread Crumbs:
       > Exception raised in: Core.Marketplace.Markets.Market.read
       > Exception raised in: Core.Marketplace.Markets.Property.all

     Unknown Error

     * ** (Ecto.Query.CompileError) only one select expression is allowed in query
       (ash_postgres 2.4.15) lib/data_layer.ex:1126: AshPostgres.DataLayer.lateral_join_query/3
       (ash_postgres 2.4.15) lib/data_layer.ex:971: AshPostgres.DataLayer.run_query_with_lateral_join/4
       (ash 3.4.51) lib/ash/actions/read/read.ex:2791: Ash.Actions.Read.run_query/4
       (ash 3.4.51) lib/ash/actions/read/read.ex:600: anonymous fn/8 in Ash.Actions.Read.do_read/5
       (ash 3.4.51) lib/ash/actions/read/read.ex:944: Ash.Actions.Read.maybe_in_transaction/3
       (ash 3.4.51) lib/ash/actions/read/read.ex:325: Ash.Actions.Read.do_run/3
       (ash 3.4.51) lib/ash/actions/read/read.ex:82: anonymous fn/3 in Ash.Actions.Read.run/3
       (ash 3.4.51) lib/ash/actions/read/read.ex:81: Ash.Actions.Read.run/3
       (ash 3.4.51) lib/ash/actions/read/relationships.ex:427: anonymous fn/2 in Ash.Actions.Read.Relationships.do_fetch_related_records/4
       (ash 3.4.51) lib/ash/actions/read/relationships.ex:79: Ash.Actions.Read.Relationships.fetch_related_records/4
       (ash 3.4.51) lib/ash/actions/read/relationships.ex:24: Ash.Actions.Read.Relationships.load/4
       (ash 3.4.51) lib/ash/actions/read/read.ex:354: Ash.Actions.Read.do_run/3
       (ash 3.4.51) lib/ash/actions/read/read.ex:82: anonymous fn/3 in Ash.Actions.Read.run/3
       (ash 3.4.51) lib/ash/actions/read/read.ex:81: Ash.Actions.Read.run/3
       (ash 3.4.51) lib/ash.ex:2004: Ash.read/2
       test/core/marketplace/markets/property_test.exs:959: Core.Marketplace.Markets.PropertyTest."test read_admin/0 should return all properties, even draft or inactive"/1
       (ex_unit 1.17.2) lib/ex_unit/runner.ex:485: ExUnit.Runner.exec_test/2
       (stdlib 6.1.1) timer.erl:590: :timer.tc/2

The read action for both markets and regions are just the default read action.

Any idea on what is wrong here?

sezaru · January 3, 2025, 2:43pm

Update:

I have the same issue in this other case:

I have the following load in my read action:

                load: [lead: [:notes, :property_appointment]]

That will use this relationship:

    belongs_to :lead, Ap.Lead do
      attribute_writable? true

      public? true
    end

Which will load this other relationship:

    has_one :property_appointment, Ap.PropertyAppointment do
      destination_attribute :property_registry_id
      source_attribute :property_registry_id

      public? true

      sort created_ts: :desc
    end

And here is the error:

     ** (Ash.Error.Unknown)
     Bread Crumbs:
       > Exception raised in: Core.Marketplace.Ap.PropertyAppointment.read
       > Exception raised in: Core.Marketplace.Ap.Lead.read
       > Exception raised in: Core.Marketplace.Ap.Appointment.list_own

     Unknown Error

     * ** (Ecto.Query.CompileError) only one select expression is allowed in query
       (ash_postgres 2.4.15) lib/data_layer.ex:1117: AshPostgres.DataLayer.lateral_join_query/3
       (ash_postgres 2.4.15) lib/data_layer.ex:971: AshPostgres.DataLayer.run_query_with_lateral_join/4
       (ash 3.4.51) lib/ash/actions/read/read.ex:2791: Ash.Actions.Read.run_query/4
       (ash 3.4.51) lib/ash/actions/read/read.ex:600: anonymous fn/8 in Ash.Actions.Read.do_read/5
       (ash 3.4.51) lib/ash/actions/read/read.ex:944: Ash.Actions.Read.maybe_in_transaction/3
       (ash 3.4.51) lib/ash/actions/read/read.ex:325: Ash.Actions.Read.do_run/3
       (ash 3.4.51) lib/ash/actions/read/read.ex:82: anonymous fn/3 in Ash.Actions.Read.run/3
       (ash 3.4.51) lib/ash/actions/read/read.ex:81: Ash.Actions.Read.run/3
       (ash 3.4.51) lib/ash/actions/read/relationships.ex:453: anonymous fn/2 in Ash.Actions.Read.Relationships.do_fetch_related_records/4
       (ash 3.4.51) lib/ash/actions/read/relationships.ex:79: Ash.Actions.Read.Relationships.fetch_related_records/4
       (ash 3.4.51) lib/ash/actions/read/relationships.ex:24: Ash.Actions.Read.Relationships.load/4
       (ash 3.4.51) lib/ash/actions/read/read.ex:354: Ash.Actions.Read.do_run/3
       (ash 3.4.51) lib/ash/actions/read/read.ex:82: anonymous fn/3 in Ash.Actions.Read.run/3
       (ash 3.4.51) lib/ash/actions/read/read.ex:81: Ash.Actions.Read.run/3
       (ash 3.4.51) lib/ash/actions/read/relationships.ex:606: anonymous fn/3 in Ash.Actions.Read.Relationships.do_fetch_related_records/4
       (ash 3.4.51) lib/ash/actions/read/relationships.ex:79: Ash.Actions.Read.Relationships.fetch_related_records/4
       (ash 3.4.51) lib/ash/actions/read/relationships.ex:24: Ash.Actions.Read.Relationships.load/4
       (ash 3.4.51) lib/ash/actions/read/read.ex:354: Ash.Actions.Read.do_run/3
     stacktrace:
       (ash_postgres 2.4.15) lib/data_layer.ex:1117: AshPostgres.DataLayer.lateral_join_query/3
       (ash_postgres 2.4.15) lib/data_layer.ex:971: AshPostgres.DataLayer.run_query_with_lateral_join/4
       (ash 3.4.51) lib/ash/actions/read/read.ex:2791: Ash.Actions.Read.run_query/4
       (ash 3.4.51) lib/ash/actions/read/read.ex:600: anonymous fn/8 in Ash.Actions.Read.do_read/5
       (ash 3.4.51) lib/ash/actions/read/read.ex:944: Ash.Actions.Read.maybe_in_transaction/3
       (ash 3.4.51) lib/ash/actions/read/read.ex:325: Ash.Actions.Read.do_run/3
       (ash 3.4.51) lib/ash/actions/read/read.ex:82: anonymous fn/3 in Ash.Actions.Read.run/3
       (ash 3.4.51) lib/ash/actions/read/read.ex:81: Ash.Actions.Read.run/3
       (ash 3.4.51) lib/ash/actions/read/relationships.ex:453: anonymous fn/2 in Ash.Actions.Read.Relationships.do_fetch_related_records/4
       (ash 3.4.51) lib/ash/actions/read/relationships.ex:79: Ash.Actions.Read.Relationships.fetch_related_records/4
       (ash 3.4.51) lib/ash/actions/read/relationships.ex:24: Ash.Actions.Read.Relationships.load/4
       (ash 3.4.51) lib/ash/actions/read/read.ex:354: Ash.Actions.Read.do_run/3
       (ash 3.4.51) lib/ash/actions/read/read.ex:82: anonymous fn/3 in Ash.Actions.Read.run/3
       (ash 3.4.51) lib/ash/actions/read/read.ex:81: Ash.Actions.Read.run/3
       (ash 3.4.51) lib/ash/actions/read/relationships.ex:606: anonymous fn/3 in Ash.Actions.Read.Relationships.do_fetch_related_records/4
       (ash 3.4.51) lib/ash/actions/read/relationships.ex:79: Ash.Actions.Read.Relationships.fetch_related_records/4
       (ash 3.4.51) lib/ash/actions/read/relationships.ex:24: Ash.Actions.Read.Relationships.load/4
       (ash 3.4.51) lib/ash/actions/read/read.ex:354: Ash.Actions.Read.do_run/3

zachdaniel · January 3, 2025, 6:08pm

Please make sure you’re on the latest ash_postgres and ash_sql

sezaru · January 3, 2025, 6:29pm

Thanks Zach, that was it!