Owens

How to read Postgrex SQL ERROR 42703

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?

6 comments

#postgres #postgrex #postgresql

0 1948 6

2020-11-15 15:40:10 UTC

First 6 of 6 Posts

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

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

Post #1

Owens

Hi again @benwilson512

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()

Post #2

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

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]

Post #3

Owens

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!

Post #4

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

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 Ecto.Schema — Ecto v3.14.0. I almost always use an actual schema in the middle and not many_to_many.

Post #5