Ash Elixir•3y ago

Lookups/calcultions based on has_many relationships

I will try to be very precise and concise, so apologies if this comes off rude-ish. - The history table contains a long list of entires that a user enters the application. Imagine like a list of chat entries. - The users can bookmark any number of those history entries - when retrieving a list of entries (e.g., last 40 entries, or all entries in a paged manner) I would like to try and in one go retrieve both the entries and whether they were bookmarked by this particular user A corresponding SQL query would be something like

SELECT history.*, 
       CASE WHEN bookmarks.history_id IS NULL THEN 0 ELSE 1 END AS is_bookmarked
FROM history
LEFT JOIN bookmarks ON history.id = bookmarks.history_id;

SELECT history.*, 
       CASE WHEN bookmarks.history_id IS NULL THEN 0 ELSE 1 END AS is_bookmarked
FROM history
LEFT JOIN bookmarks ON history.id = bookmarks.history_id;

I have it sort of figured out with relationships and calculations, but this seems to load more data than I would actually like. Relevant parts only:

defmodule Telepai.App.History do
  attributes do
    integer_primary_key(:id)
    
    attribute :data, :string do
      allow_nil?(false)
      constraints(trim?: true, max_length: 5000, allow_empty?: false)
    end
  end

  relationships do
    has_many :bookmarks, Telepai.Users.Bookmark do
      api(Telepai.Users)

      # I have played with hardcoded filters, too.
      # But of course I don't want it hardcoded in the end :)
      # filter(expr(user_id == 1))
    end
  end

  calculations do
    # hardcoded again
    calculate(:is_bookmarked, :term, expr(bookmarks.user_id == 1))

    # Or even this, but this needs the hardcoded filter in the relationship
    # calculate(:is_bookmarked, :term, expr(not is_nil(bookmarks)))
  end

end

defmodule Telepai.App.History do
  attributes do
    integer_primary_key(:id)
    
    attribute :data, :string do
      allow_nil?(false)
      constraints(trim?: true, max_length: 5000, allow_empty?: false)
    end
  end

  relationships do
    has_many :bookmarks, Telepai.Users.Bookmark do
      api(Telepai.Users)

      # I have played with hardcoded filters, too.
      # But of course I don't want it hardcoded in the end :)
      # filter(expr(user_id == 1))
    end
  end

  calculations do
    # hardcoded again
    calculate(:is_bookmarked, :term, expr(bookmarks.user_id == 1))

    # Or even this, but this needs the hardcoded filter in the relationship
    # calculate(:is_bookmarked, :term, expr(not is_nil(bookmarks)))
  end

end

And the bookmarks:

defmodule Telepai.Users.Bookmark do
  attributes do
    integer_primary_key(:id)
  end

  relationships do
    belongs_to :user, Telepai.Users.User do
      allow_nil?(false)
    end

    belongs_to :history, Telepai.App.History do
      api(Telepai.App.History)
      allow_nil?(false)
    end
  end
end

defmodule Telepai.Users.Bookmark do
  attributes do
    integer_primary_key(:id)
  end

  relationships do
    belongs_to :user, Telepai.Users.User do
      allow_nil?(false)
    end

    belongs_to :history, Telepai.App.History do
      api(Telepai.App.History)
      allow_nil?(false)
    end
  end
end

Continued below

12 Replies

dmitriidOP•3y ago

I end up needing to load :bookmarks before I can calculate :is_bookmarked:

Telepai.App.get!(Telepai.App.History, 1, load: :bookmarks) 
|> Telepai.App.load!(:is_bookmarked)

Telepai.App.get!(Telepai.App.History, 1, load: :bookmarks) 
|> Telepai.App.load!(:is_bookmarked)

For one resource, as above, this queries the database twice: once for history, and once for the related bookmarks. When retrieving a list of history entries, it will once again produce to queries to the database: once for history, and once for bookmarks (seemingly retrieving all bookmarks). Is there a way to massage Ash to produce just one query? Thank you!

ZachDaniel•3y ago

So are you saying that it wouldn't let you load :is_bookmarked without loading :bookmarks? oh, yeah actually I see thats actually a bug that its letting you do that 🙂 calculations cannot refer to relationships, only aggregates. What I think you want here, though is a combination of exists and a calculation argument

calculate :is_bookmarked, :boolean, expr(exists(bookmarks, id == ^arg(:user_id))) do
  argument :user_id, :uuid, allow_nil?: false
end

calculate :is_bookmarked, :boolean, expr(exists(bookmarks, id == ^arg(:user_id))) do
  argument :user_id, :uuid, allow_nil?: false
end

dmitriidOP•3y ago

Ah. Brilliant. This looks exactly like what I'd want. Only now I have no idea how to load this calculation with the arguments 🙂

ZachDaniel•3y ago

load!(is_bookmarked: %{user_id: user_id}) will do it 😄 And you can filter on it like this:

query |> Ash.Query.filter(expr(is_bookmarked(user_id: user_id)))

query |> Ash.Query.filter(expr(is_bookmarked(user_id: user_id)))

dmitriidOP•3y ago

Hmmm.. Something's funky 🙂

> Telepai.App.History |> Ash.Query.filter(expr(is_bookmarked(user_id: 1))) |> Telepai.App.read!()

** (Ash.Error.Unknown) Unknown Error

* filter: is invalid

> Telepai.App.History |> Ash.Query.filter(expr(is_bookmarked(user_id: 1))) |> Telepai.App.read!()

** (Ash.Error.Unknown) Unknown Error

* filter: is invalid

> Telepai.App.History |> Telepai.App.get!(1) |> Telepai.App.load!(is_bookmarked: %{user_id: 1})

AshPostgres.DataLayer.run_query/2, at: lib/data_layer.ex:613
** (BadMapError) expected a map, got: {:error, "is invalid"}

> Telepai.App.History |> Telepai.App.get!(1) |> Telepai.App.load!(is_bookmarked: %{user_id: 1})

AshPostgres.DataLayer.run_query/2, at: lib/data_layer.ex:613
** (BadMapError) expected a map, got: {:error, "is invalid"}

> Telepai.App.History |> Ash.Query.for_read(:read) |> Ash.Query.load(is_bookmarked: %{user_id: 1}) |> Telepai.App.read!()
** (RuntimeError) Telepai.GPT.read!/2 expected an %Ash.Query{} or an Ash Resource but instead got {:error, "is invalid"}

> Telepai.App.History |> Ash.Query.for_read(:read) |> Ash.Query.load(is_bookmarked: %{user_id: 1}) |> Telepai.App.read!()
** (RuntimeError) Telepai.GPT.read!/2 expected an %Ash.Query{} or an Ash Resource but instead got {:error, "is invalid"}

ZachDaniel•3y ago

🤔 that is weird So does that mean Ash.Query.load(is_bookmarked: %{user_id: 1}) is returning {:error, "is invalid"}

dmitriidOP•3y ago

Ah I figured it out argument :user_id, :uuid, allow_nil?: false This should be :integer because my ids are integers

ZachDaniel•3y ago

Makes sense, but Ash.Query.load should always return a query so that might be something I need to look at sounds pretty easily reproducible I'll take a look in the next day or two

dmitriidOP•3y ago

And it looks like Ash.Query returns {:error, "is invalid"} in this case

ZachDaniel•3y ago

got it, thanks for letting me know I'll fix it 🙂

dmitriidOP•3y ago

No worries. This is brilliant anyway 🙂 Forgot to say thank you 🙂 Thank you!

ZachDaniel•3y ago

no worries 😆 happy to help!

Gaming

Programming

Lookups/calcultions based on has_many relationships

Did you find this page helpful?