Using join column value in update_all

Questions / Help
,
1

Is it possible to use a field from a join in an update.

I’m trying to do something like this:

    Repo.update_all(
      from(m in Model,
        join: s in subquery(model_scores_query), on: s.model_id == m.id),
        update: [set: [weight: s.weight_score]]
      )

I want the ‘weight’ field of model to be set to the subquery’s ‘weight_score’ value but the compiler gives the following error in the ‘update: [set: [weight: s.weight_score]]:’ line

error:undefined function s/0

4 Likes
2

Hmm, seems odd that that wouldn’t work, maybe you could just use a fragment to try to ‘coerce’ it? Maybe instead of s.weight_score try fragment("?", s.weight_score) or something? Sounds like a bug though?

3

Snap. The closing parenthesis on the queryable needs to moved to the outside of the update keyword. This is too little, too late, but I hope this helps someone. To access the reference to the joined entity, you essentially have to ignore the second argument to the update_all function and use update as an option within your first argument, the queryable. See the last 3 examples at: https://hexdocs.pm/ecto/Ecto.Repo.html#c:update_all/3.

You are going for:

  Repo.update_all(
    from(m in Model,
      join: s in subquery(model_scores_query),
      on: s.model_id == m.id,
      update: [set: [weight: s.weight_score]]
    ),
    []
  )

At times I have a hard time distinguishing where a query ends and another function argument begins. Especially here, where the update option for the queryable seems redundant with the second argument of the update_all function. But my experience with elixir has shown with that the variety of ways you can use the available functions is often very powerful. The variety in the Ecto module usage has been amazing at handling all the necessary use cases and ways one can compose queries, etc.

3 Likes
4

Good catch! The update in the query is the Ecto.Query.update/3 macro for anyone curious (like I was).