Ecto query association by a has_one field

Questions / Help
1

I’m building a query that is attempting to find all “Predictions” where the “Prediction Score” column is nil:

query =
      from(
        p in Prediction,
        where: p.team_id == ^team.id,
        where: is_nil(p.prediction_score)
      )

    Repo.all(query)

But I’m getting this error:

** (Postgrex.Error) ERROR 42703 (undefined_column) column p0.prediction_score does not exist

The prediction_score column is a has_one field on Prediction:

schema "predictions" do
    belongs_to(:league, League, foreign_key: :league_id, type: :binary_id)
    belongs_to(:team, Team, foreign_key: :team_id, type: :binary_id)
    belongs_to(:question, Question, foreign_key: :question_id, type: :binary_id)
    has_one(:prediction_score, PredictionScore) <--- HERE
    embeds_one(:details, Details)

    timestamps()
end

Does ecto/postgrex not see the has_one as a column on that table? Is there some nice way I can achieve this functionality that I’m not aware of?

1 Like
2

Usually the in-database column for a has_one relation (or any relation really) would be p.prediction_score_id. I haven’t worked with Ecto in a while so I can’t remember if that matters. Can you try the query with where: is_nil(p.prediction_score_id) and see what happens?

3

hi @Cam associations are not columns, so they can’t be queried like columns. To do the query you want, you need to do a left join and then look for nil prediction score columns:

from(p in Prediction,
  where: p.team_id == ^team.id,
  left_join: score in assoc(p, :prediction_score),
  where: is_nil(score.id))
4 Likes
4

Thanks @benwilson512! That makes complete sense now that you laid that out. Appreciate the help.