How to read Postgrex SQL ERROR 42703

Questions / Help
, ,
1

Hello all,

I’m scratching my head trying to read this error log from Postgrex.

** (Postgrex.Error) ERROR 42703 (undefined_column) column c2.user_id does not exist

    query: SELECT t0."id", t0."logo_url", t0."icon_url", t0."invite_password",
t0."is_company", t0."long_description", t0."name", t0."short_description",
t0."company_id", t0."inserted_at", t0."updated_at",
c1."id" FROM "teams" AS t0 INNER JOIN "cohorts"
AS c1 ON c1."id" = ANY($1) INNER JOIN  "cohorts_teams"
AS c2 ON c2."cohort_id" = c1."id" WHERE (c2."user_id" = t0."id") ORDER BY c1."id"

Here’s the function call.

teams_query = Team |> preload(:teams_users)
organization_query = Organization |> preload(:organizations_users)

cohort = Cohort
|> preload([teams: ^teams_query, organization: ^organization_query])
|> Repo.get!(cohort_id)

It appears to be looking for user_id on the cohorts_teams table, which doesn’t exist. If so, why could it be looking for this?

2

Hi @Owens this is likely an isue with how you have defined your associations, can you show your schema modules?

3

Hi again @benwilson512 :slight_smile:

schema "cohorts" do
    field :first_day, :date
    field :has_stages, :boolean, default: false
    field :invite_password, :string
    field :last_day, :date
    field :name, :string
    field :team_based, :boolean, default: false
    field :teams_are_companies, :boolean, default: false
    field :time_block_count, :integer
    belongs_to :organization, Rocketship.Organizations.Organization
    many_to_many :users, Rocketship.Accounts.User, join_through: "cohorts_users"
    has_many :cohorts_users, Rocketship.Cohorts.CohortUser
    many_to_many :teams, Rocketship.Accounts.User, join_through: "cohorts_teams"
    has_many :cohorts_teams, Rocketship.Cohorts.CohortTeam
    timestamps()

schema "cohorts_teams" do
    belongs_to :cohort, Rocketship.Cohorts.Cohort
    belongs_to :team, Rocketship.Cohorts.Team
    timestamps()

schema "teams" do
    field :logo_url, :string
    field :icon_url, :string
    field :invite_password, :string
    field :is_company, :boolean, default: false
    field :long_description, :string
    field :name, :string
    field :short_description, :string
    field :company_id, :id
    many_to_many :users, Rocketship.Accounts.User, join_through: "teams_users"
    has_many :teams_users, Rocketship.Cohorts.TeamUser
    many_to_many :cohorts, Rocketship.Accounts.User, join_through: "cohorts_teams"
    has_many :cohorts_teams, Rocketship.Cohorts.CohortTeam
    timestamps()
4

You have a mistake here: many_to_many :teams, Rocketship.Accounts.User, join_through: "cohorts_teams". This should be Rocketship.Accounts.Team probably.

Also, if you use join_through and then a string it won’t use the schema, you need to join_through: [:cohorts_teams, :team]

1 Like
5

Also, if you use join_through and then a string it won’t use the schema, you need to join_through: [:cohorts_teams, :team]

Interesting I hadn’t read that before. Is there documentation to explain this? The Ecto Association guide only shows join_through with string.

Thanks for your insights as always, Ben!

6

As a first note, the stuff you’re linking to are quite old, Ecto 3.X has been out for a while.

Secondly, I was a bit wrong now that I looked at it, I got join_through: confused with has_many through https://hexdocs.pm/ecto/Ecto.Schema.html#has_many/3-options. I almost always use an actual schema in the middle and not many_to_many.

1 Like
7

Thanks for the reference!

Strange I came upon the 2.2 docs through Google and didn’t notice it was the old version.