JoinTable with extra fields

Questions / Help
, ,
1

Hey there,

I am trying to find an elegant way to populate a field on a join table in a json response.
I got the following schemas:

schema "characters" do
  field :name, :string
  
  has_many :characters_skills, Skill
  has_many :skills, through: [:characters_skills, :skill]
end

schema "talents" do
  field :name, :string

  field :times_taken, :string, virtual: true

  has_many :characters_talents, Character.Talent
  has_many :characters, through: [:characters_talents, :character]

  timestamps()
end

schema "characters_talents" do
  belongs_to(:character, Character, primary_key: true)
  belongs_to(:talent, Abilities.Talent, primary_key: true)

  field :times_taken, :integer

  timestamps()
end

Now in my response I don’t want characters_talents but rather just talents containing times_taken.
My current approach is this:

    import Ecto.Query

    from(c in Character)
    |> preload([:talents])
    |> join(:inner, [c], t in assoc(c, :talents))
    |> join(:inner, [c, t], ct in assoc(c, :characters_talents))
    |> select_merge([c, t, ct], %{talents: merge(t, %{times_taken: ct.times_taken})})

which works fine, as soon as I switch to :left_join because t might be empty merge/2 raises.

Is the anything I don’t see? Anything I could do more efficient? I am asking especially because my character schema has at least 5 more relations like this.

Thanks,
Nick

Also might be worse mentioning, that I opened a proposal here:
https://groups.google.com/g/elixir-ecto/c/vLAfANvnVy4/m/wS8TSMySBQAJ
So merge/2 might except nil values.

2

Hello,

I don’t understand the use case. Could you describe what do you do with this data set? Why do you want a list of duplicated characters, but each with different talent? Also, talents will be overwritten by preload.

3

Hey,
thank you for your answer.

Why do you want a list of duplicated characters, but each with different talent

That’s not the case. I know the query is not 100% correct. I want a single character or a list of characters that contain all talents but the talents should contain the field of the join table characters_talents

All in all I am looking for a possibility to load the times_taken field without having to iterate through the final struct

4

Are you sure? IMO the join records (characters_talents) are the important data here. Most operations you’d want to do with “talents” are really operations on them:

  • upgrading a character by taking a talent again → increment times_taken on the join record
  • reading the list of talents for a given character → needs (as you’ve identified) both the talent details and the count, which are exactly what’s in characters_talents

Calling the schema something less unwieldy like talent_assignments might help, FWIW

1 Like
5

Thank you,
you sure are right about the name, could be better.

You are absolutely right about the update, but I am not sure about the reading.
Lets say I want to initially load the character, I don’t want to to n queries to all relation but one query to load (rule) them all. the final result should be:

%{
  name: "Farellion Glutsturz",
  talents: [
    full_detailed_talents
  ]
}

So my entry point is the character. If I query for the character and preload those association:

    from(c in Character)
    |> preload([:talents])

I get both talents and charactes_talents. Which requires me to iterate through talents to find the matching times_taken from characters_talents. Or I have to live with one more nesting, which in my opinion might not be the smartest api design:

%{
  name: "Farellion Glutsturz",
  characters_talents: [
    %{
         times_taken: 5,
         talent: %{}
      },...
  ]
}
6

@al2o3cr I gave it some thoughts and I think I might reconsider my API structure, so thanks for your answer again.

Still I think my proposal (first post ecto group) might be helpful in some use cases. Not this particular thou.

Greetings and have nice week :slight_smile: