Ash Elixir•3y ago

Issues with sorting on `first` aggregate

actions do
  defaults [:create, :read, :update, :destroy]

  read :published do
    prepare fn query, _context ->
      query
      |> Ash.Query.filter(air_dates.status == :published)
      |> Ash.Query.sort(latest_air_date: :desc)
      |> Ash.Query.limit(10)
    end

    # pagination do
    #   offset? true
    #   default_limit 10
    #   countable :by_default
    # end
  end
end
...
preparations do
  prepare fn query, _context ->
    query
    |> Ash.Query.load([:thumb_url, :video_file_url, :video_poster_url])
  end
end

aggregates do
  first :latest_air_date, :air_dates, :start do
    sort start: :desc
  end
end

actions do
  defaults [:create, :read, :update, :destroy]

  read :published do
    prepare fn query, _context ->
      query
      |> Ash.Query.filter(air_dates.status == :published)
      |> Ash.Query.sort(latest_air_date: :desc)
      |> Ash.Query.limit(10)
    end

    # pagination do
    #   offset? true
    #   default_limit 10
    #   countable :by_default
    # end
  end
end
...
preparations do
  prepare fn query, _context ->
    query
    |> Ash.Query.load([:thumb_url, :video_file_url, :video_poster_url])
  end
end

aggregates do
  first :latest_air_date, :air_dates, :start do
    sort start: :desc
  end
end

Originally posted by @l00ker , moved to a support thread

33 Replies

ZachDanielOP•3y ago

Lets chat here 🙂 How are you invoking the action? like running Api.read? Or via an api extension?

l00ker•3y ago

There's a code_interface:

code_interface do
  define_for TVEpisodes.Api
  define :published
end

code_interface do
  define_for TVEpisodes.Api
  define :published
end

So I'm doing TVEpisodes.TVEpisode.published()

ZachDanielOP•3y ago

so strange that we're getting different queries Try not loading the calculations as well Lets try to trim it down to its bare minimum maybe even remove

      |> Ash.Query.filter(air_dates.status == :published)

l00ker•3y ago

With the air_dates action I'm just doing TVEpisodes.AirDate |> Ash.Query.for_read(:published_episodes) |> TVEpisodes.Api.read!() So you want the SQL for that?

ZachDanielOP•3y ago

first, are they still out of order?

l00ker•3y ago

No they are in order with the raw SQL There are some with different statuses in the dataset I'm using so I had to check

ZachDanielOP•3y ago

Okay, so lets add bits back in until it breaks start with the published filter

l00ker•3y ago

I just added the published filter back and it's not in order

ZachDanielOP•3y ago

🤔 Can you also load the aggregate? How are you confirming they aren't in order?

l00ker•3y ago

I'm just running a query in psql to get the titles

ZachDanielOP•3y ago

can you add Ash.Query.load(:latest_air_date) to your query? And confirm that the latest air dates its returning are in fact out of order

l00ker•3y ago

When I do the load, the dates seem to be descending by month but starting in 2011 It's like it's get the last 10 results This dataset goes back a few years

ZachDanielOP•3y ago

🤔 So it could be in reverse order Yeah I just pushed something up to main could you try that and lemme know how it goes?

l00ker•3y ago

So it's like this (latest_air_date only)

latest_air_date: ~U[2012-06-01 15:00:00Z],
latest_air_date: ~U[2012-05-31 15:00:00Z],
# Big gap here
latest_air_date: ~U[2011-12-27 16:00:00Z],
latest_air_date: ~U[2011-12-26 16:00:00Z],
latest_air_date: ~U[2011-12-02 16:00:00Z],
latest_air_date: ~U[2011-12-01 16:00:00Z],
latest_air_date: ~U[2011-11-30 16:00:00Z],
latest_air_date: ~U[2011-11-29 16:00:00Z],
latest_air_date: ~U[2011-11-28 16:00:00Z],
# Big gap here
latest_air_date: ~U[2011-08-12 21:00:00Z],

latest_air_date: ~U[2012-06-01 15:00:00Z],
latest_air_date: ~U[2012-05-31 15:00:00Z],
# Big gap here
latest_air_date: ~U[2011-12-27 16:00:00Z],
latest_air_date: ~U[2011-12-26 16:00:00Z],
latest_air_date: ~U[2011-12-02 16:00:00Z],
latest_air_date: ~U[2011-12-01 16:00:00Z],
latest_air_date: ~U[2011-11-30 16:00:00Z],
latest_air_date: ~U[2011-11-29 16:00:00Z],
latest_air_date: ~U[2011-11-28 16:00:00Z],
# Big gap here
latest_air_date: ~U[2011-08-12 21:00:00Z],

Okay. I'll let ya know probably sometime tomorrow. Thanks for your time Zach!

ZachDanielOP•3y ago

no problem 🙂 This is a pretty high priority bug in my book so just let me know once you've tried it out 🙂 I'll also try and reproduce it myself

l00ker•3y ago

10-4

ZachDanielOP•3y ago

okay, so I fixed the issue, but its not the most optimal behavior that it has right now Luckily for you, when I find a better way to do it, you won't have to change anything aside from upgrading ash_postgres although there is way to make it more optimized

|> Ash.Query.filter(exists(air_dates, status == :published))

|> Ash.Query.filter(exists(air_dates, status == :published))

they are semantically equivalent (which should tell you something about how I'll implement the optimization) Going to mark this as solved, feel free to point out if it doesn't work (after updating to latest main again)

l00ker•3y ago

Just updated to ash_postgres main a few min before this post and ran the query. It's close, but still not right. 😦 I removed most of the attributes leaving just the id & title and removed the calculations etc. Here is the stripped down module right now with your changes:

defmodule TVEpisodes.TVEpisode do
  use Ash.Resource,
    data_layer: AshPostgres.DataLayer

  require Ash.Query

  postgres do
    table "tv_episodes"
    repo Wog.Repo
  end

  code_interface do
    define_for TVEpisodes.Api
    define :published
  end

  actions do
    defaults [:create, :read, :update, :destroy]

    read :published do
      prepare fn query, _context ->
        query
        |> Ash.Query.filter(exists(air_dates, status == :published))
        |> Ash.Query.sort(latest_air_date: :desc)
        |> Ash.Query.load(:air_dates)
        |> Ash.Query.limit(10)
      end
    end
  end

  attributes do
    integer_primary_key :id
    attribute :title, :string
  end

  relationships do
    has_many :air_dates, TVEpisodes.AirDate do
      destination_attribute :episode_id
    end
  end

  aggregates do
    first :latest_air_date, :air_dates, :start do
      sort start: :desc
    end
  end
end

defmodule TVEpisodes.TVEpisode do
  use Ash.Resource,
    data_layer: AshPostgres.DataLayer

  require Ash.Query

  postgres do
    table "tv_episodes"
    repo Wog.Repo
  end

  code_interface do
    define_for TVEpisodes.Api
    define :published
  end

  actions do
    defaults [:create, :read, :update, :destroy]

    read :published do
      prepare fn query, _context ->
        query
        |> Ash.Query.filter(exists(air_dates, status == :published))
        |> Ash.Query.sort(latest_air_date: :desc)
        |> Ash.Query.load(:air_dates)
        |> Ash.Query.limit(10)
      end
    end
  end

  attributes do
    integer_primary_key :id
    attribute :title, :string
  end

  relationships do
    has_many :air_dates, TVEpisodes.AirDate do
      destination_attribute :episode_id
    end
  end

  aggregates do
    first :latest_air_date, :air_dates, :start do
      sort start: :desc
    end
  end
end

ZachDanielOP•3y ago

😢

l00ker•3y ago

This is the first item of the result after running TVEpisodes.TVEpisode.published(). I loaded the :air_dates to help illustrate the issue I'm seeing:

#TVEpisodes.TVEpisode<
  latest_air_date: ~U[2022-11-11 18:00:00Z],
  air_dates: [
    #TVEpisodes.AirDate<
      episode: #Ash.NotLoaded<:relationship>,
      __meta__: #Ecto.Schema.Metadata<:loaded, "tv_episodes_air_dates">,
      id: 8802,
      start: ~U[2022-05-25 17:00:00Z],
      end: ~U[2022-05-25 17:30:00Z],
      status: :published,
      episode_id: 2213,
      aggregates: %{},
      calculations: %{},
      __order__: nil,
      ...
    >,
    #TVEpisodes.AirDate<
      episode: #Ash.NotLoaded<:relationship>,
      __meta__: #Ecto.Schema.Metadata<:loaded, "tv_episodes_air_dates">,
      id: 8803,
      start: ~U[2022-11-11 18:00:00Z],
      end: ~U[2022-11-11 18:30:00Z],
      status: :scheduled,
      episode_id: 2213,
      aggregates: %{},
      calculations: %{},
      __order__: nil,
      ...
    >
  ],
  __meta__: #Ecto.Schema.Metadata<:loaded, "tv_episodes">,
  id: 2213,
  title: "WGR11795 - Up Close & Personal with Steve Weidenkopf",
  aggregates: %{},
  calculations: %{},
  __order__: nil,
  ...
>

#TVEpisodes.TVEpisode<
  latest_air_date: ~U[2022-11-11 18:00:00Z],
  air_dates: [
    #TVEpisodes.AirDate<
      episode: #Ash.NotLoaded<:relationship>,
      __meta__: #Ecto.Schema.Metadata<:loaded, "tv_episodes_air_dates">,
      id: 8802,
      start: ~U[2022-05-25 17:00:00Z],
      end: ~U[2022-05-25 17:30:00Z],
      status: :published,
      episode_id: 2213,
      aggregates: %{},
      calculations: %{},
      __order__: nil,
      ...
    >,
    #TVEpisodes.AirDate<
      episode: #Ash.NotLoaded<:relationship>,
      __meta__: #Ecto.Schema.Metadata<:loaded, "tv_episodes_air_dates">,
      id: 8803,
      start: ~U[2022-11-11 18:00:00Z],
      end: ~U[2022-11-11 18:30:00Z],
      status: :scheduled,
      episode_id: 2213,
      aggregates: %{},
      calculations: %{},
      __order__: nil,
      ...
    >
  ],
  __meta__: #Ecto.Schema.Metadata<:loaded, "tv_episodes">,
  id: 2213,
  title: "WGR11795 - Up Close & Personal with Steve Weidenkopf",
  aggregates: %{},
  calculations: %{},
  __order__: nil,
  ...
>

Notice the latest_air_date: ~U[2022-11-11 18:00:00Z]. It matches the second air_date which has status: :scheduled. So I think what's happening is that the query is ordering the air_dates by start: :desc and the first aggregate is using the first air_date in the array to pick the episode id with the status being ignored in that case.

ZachDanielOP•3y ago

I think that actually makes sense? The latest_air_date doesn't have a filter on status == :published

    first :latest_air_date, :air_dates, :start do
      sort start: :desc
      filter expr(status == :published)
    end

    first :latest_air_date, :air_dates, :start do
      sort start: :desc
      filter expr(status == :published)
    end

l00ker•3y ago

Sorry for the late reply BTW. I got caught up with something and couldn't get away.

ZachDanielOP•3y ago

no worries 😄

l00ker•3y ago

Lemme try that. Success! 🥳 That nailed it!

ZachDanielOP•3y ago

🎉 It definitely takes some getting used to building with these building blocks, and its a bummer that you had these issues (although good for Ash in general because they are fixed and I've also added some optimizations around them in the process) but the nice thing about it is that these pieces are reusable and composable, in increasingly useful fashions i.e latest_air_date can be loaded directly, or filtered on, that kind of thing

l00ker•3y ago

Yes. I can see that and that's why I hung in there!

ZachDanielOP•3y ago

noice 😄

l00ker•3y ago

I got to learn a few things here as well

ZachDanielOP•3y ago

Another thing you might do is change this:

        |> Ash.Query.filter(not is_nil(latest_air_date))

        |> Ash.Query.filter(not is_nil(latest_air_date))

That ought to perform better since it will use the aggregate that you're already loading And then, to further enhance that, you could potentially do this:

calculations do
  calculate :has_aired, :boolean, expr(not is_nil(latest_air_date))
end

calculations do
  calculate :has_aired, :boolean, expr(not is_nil(latest_air_date))
end

At which point you can say

Ash.Query.filter(has_aired)

Ash.Query.filter(has_aired)

l00ker•3y ago

So you're saying to add that as an additional filter?

ZachDanielOP•3y ago

I'd replace your existing filter with that calculation

l00ker•3y ago

Ahh The more time I get under my belt with Ash I should start to see things better. I know I've already said it before, but thank you for hanging in there with me!

ZachDanielOP•3y ago

Its my pleasure 😄

Gaming

Programming

Issues with sorting on `first` aggregate

Did you find this page helpful?