26 replies

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;

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

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

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

Lookups/calcultions based on has_many relationships

Similar Threads

Similar Threads

Similar Threads