All assoc are correct but i get no assoc _id for categories

Hi everyone,

So the funny things is that I get no associations even though I specified the relationship in my migrations and my schema.

My associations

In create_articles migration i have add :category_id, references(:categories, on_delete: :delete_all)

In the category schema has_many(:articles, Article)

In articles schema I have belongs_to(:category, Category)

The relationship that I want to create is that an article can have only one category.But categories can belong to multiple articles.

Error trace

[info] GET /articles
[debug] Processing with BlogApiWeb.ArticleController.index/2
  Parameters: %{}
  Pipelines: [:browser]
[debug] QUERY ERROR source="articles" db=0.0ms queue=1.5ms
SELECT a0."id", a0."content", a0."published", a0."title", a0."category_id", a0."inserted_at", a0."updated_at" FROM "articles" AS a0 []
[info] Sent 500 in 140ms
[error] #PID<0.520.0> running BlogApiWeb.Endpoint (connection #PID<0.519.0>, stream id 1) terminated
Server: localhost:4000 (http)
Request: GET /articles
** (exit) an exception was raised:
    ** (Postgrex.Error) ERROR 42703 (undefined_column) column a0.category_id does not exist

    query: SELECT a0."id", a0."content", a0."published", a0."title", a0."category_id", a0."inserted_at", a0."updated_at" FROM "articles" AS a0
        (ecto_sql) lib/ecto/adapters/sql.ex:629: Ecto.Adapters.SQL.raise_sql_call_error/1
        (ecto_sql) lib/ecto/adapters/sql.ex:562: Ecto.Adapters.SQL.execute/5
        (ecto) lib/ecto/repo/queryable.ex:177: Ecto.Repo.Queryable.execute/4
        (ecto) lib/ecto/repo/queryable.ex:17: Ecto.Repo.Queryable.all/3
        (blog_api) lib/blog_api/blog.ex:23: BlogApi.Blog.list_articles/0
        (blog_api) lib/blog_api_web/controllers/article_controller.ex:9: BlogApiWeb.ArticleController.index/2
        (blog_api) lib/blog_api_web/controllers/article_controller.ex:1: BlogApiWeb.ArticleController.action/2
        (blog_api) lib/blog_api_web/controllers/article_controller.ex:1: BlogApiWeb.ArticleController.phoenix_controller_pipeline/2
        (phoenix) lib/phoenix/router.ex:288: Phoenix.Router.__call__/2
        (blog_api) lib/blog_api_web/endpoint.ex:1: BlogApiWeb.Endpoint.plug_builder_call/2
        (blog_api) lib/plug/debugger.ex:122: BlogApiWeb.Endpoint."call (overridable 3)"/2
        (blog_api) lib/blog_api_web/endpoint.ex:1: BlogApiWeb.Endpoint.call/2
        (phoenix) lib/phoenix/endpoint/cowboy2_handler.ex:42: Phoenix.Endpoint.Cowboy2Handler.init/4
        (cowboy) /home/dan/Codes/blog_api/deps/cowboy/src/cowboy_handler.erl:41: :cowboy_handler.execute/2
        (cowboy) /home/dan/Codes/blog_api/deps/cowboy/src/cowboy_stream_h.erl:320: :cowboy_stream_h.execute/3
        (cowboy) /home/dan/Codes/blog_api/deps/cowboy/src/cowboy_stream_h.erl:302: :cowboy_stream_h.request_process/3
        (stdlib) proc_lib.erl:249: :proc_lib.init_p_do_apply/3

Thanks in advance

hey, Postgres is looking for a column category_id in articles tables and its not there, can you please post your migrations related to articles table.
I think you may you used a different column over category_id, if thats the case you need to specify the column.

belongs_to :category, Category, foreign_key: :some_id

Hi @tenzil

I am not using any custom ids.
The current config should provide a category_id to the articles table but for some strange reason it doesn’t.
I think this may actually be a bug that happens if phoenix 1.4.11 receives to many errors and also I used mix ecto.reset.

Tomorrow i will try to create a post and categories in a new project only with relationships between them, too see if this happens again.

My migration for articles

defmodule BlogApi.Repo.Migrations.CreateArticles do
  use Ecto.Migration

  def change do
    create table(:articles) do
      add :title, :string
      add :content, :string
      add :published, :boolean, default: false, null: false
      add :category_id, references(:categories, on_delete: :delete_all)
      timestamps()
    end
  end
end

My article schema

defmodule BlogApi.Blog.Article do
  use Ecto.Schema
  import Ecto.Changeset
  alias BlogApi.Categories.Category

  schema "articles" do
    field :content, :string
    field :published, :boolean, default: false
    field :title, :string
    belongs_to(:category, Category)
    timestamps()
  end

  @doc false
  def changeset(article, attrs) do
    article
    |> cast(attrs, [:title, :content, :published])
    |> validate_required([:title, :content, :published])
    |> unique_constraint(:title)
  end
end

My schema for categories

defmodule BlogApi.Categories.Category do
  use Ecto.Schema
  import Ecto.Changeset
  alias BlogApi.Blog.Article

  schema "categories" do
    field :name, :string
    field :slug, :string

    has_many(:articles, Article)
    timestamps()
  end

  @doc false
  def changeset(category, attrs) do
    category
    |> cast(attrs, [:name, :slug])
    |> validate_required([:name, :slug])
    |> unique_constraint(:name)
  end
end
1 Like

This line in article's changeset specifies which fields will be casted for insertion. So I assume you set the category_id later in some context function with Ecto.Changeset.put_change before insertion. If not Postgresql will throws an error unless the category_id column can be null.

Also the problem may come from the submited form? You need to ensure user send a category id. Please show the part of the form where users fill or select a category.

Edit:
I think there are two ways to insert resource such as article that belongs to a parent (category in this case).

  • the category is already known before rendering the new article form. In that case generally the category id is present in the url. See nested resources. In this case you don’t cast the id in your changeset but you still need to set it before insertion.
  • the category id is provided directly from the submitted form. In this case you just need to cast as you do for the other fields. Ecto.Changeset.foreign_key_constraint can be used as validator to show appropriate error message when the id sent is not valid. Also in the form if you decide to use a select, you need to format the collection of categories to be a valid enum type. For example a list like [{value_1, id_1}, {value_2, id_2}, ... ]. Values are the options that users will read in the select, and the ids are what your changeste will cast.

I hope this will help you in fixing your issue.

1 Like

Thanks @Kurisu

So here is the part where I define my assoc on article creation

def create_article(attrs \\ %{}) do
    %Article{}
    |> Article.changeset(attrs)
    |> Ecto.Changeset.cast_assoc(:category, with: &Category.changeset/2)
    |> Repo.insert()
  end

I am using as guide the phoenix docs

https://hexdocs.pm/phoenix/contexts.html#content form here https://hexdocs.pm/phoenix/contexts.html#in-context-relationships

This part

def create_user(attrs \\ %{}) do
    %User{}
    |> User.changeset(attrs)
+   |> Ecto.Changeset.cast_assoc(:credential, with: &Credential.changeset/2)
    |> Repo.insert()
  end

For full code you can follow this link

After I set this Ecto.Changeset.foreign_key_constraint I get a more detailed error and I think it has something to do with how i used select in my form.

Complete form code

<%= form_for @changeset, @action, fn f -> %> <%= if @changeset.action do %>
<div class="alert alert-danger">
  <p>Oops, something went wrong! Please check the errors below.</p>
</div>
<% end %>

<div class="field">
  <%= label f, :title, class: "label" %>
  <div class="control">
    <%= text_input f, :title, class: "input", placeholder: "Title" %>
  </div>
  <p class="help is-danger"><%= error_tag f, :title %></p>
</div>

<div class="field">
  <%= label f, :content, class: "label" %>
  <div class="control">
    <%= textarea f, :content, class: "textarea", placeholder: "Content" %>
  </div>
  <p class="help is-danger"><%= error_tag f, :content %></p>
</div>

<div class="field">
  <%= label f, :category, class: "label" %>
  <div class="control">
    <%= select f, :category_id, @categories %>
  </div>
  <p class="help is-danger"><%= error_tag f, :category %></p>
</div>

<div class="field">
  <div class="control">
    <label class="checkbox">
      <%= checkbox f, :published %> Publish
    </label>
  </div>
  <p class="help is-danger"><%= error_tag f, :published %></p>
</div>

<div class="field is-grouped">
  <div class="control">
    <%= submit "Submit", class: "button is-info" %>
  </div>
  <div class="control">
    <%= reset "Reset", class: "button is-danger" %>
  </div>
</div>

<% end %>

I get this error when i go to the /articles/new route

Error trace

info] GET /articles/new
[debug] Processing with BlogApiWeb.ArticleController.new/2
  Parameters: %{}
  Pipelines: [:browser]
[debug] QUERY OK source="categories" db=21.9ms decode=9.7ms queue=0.6ms
SELECT c0."id", c0."name", c0."slug", c0."inserted_at", c0."updated_at" FROM "categories" AS c0 []
[
  %BlogApi.Categories.Category{
    __meta__: #Ecto.Schema.Metadata<:loaded, "categories">,
    articles: #Ecto.Association.NotLoaded<association :articles is not loaded>,
    id: 1,
    inserted_at: ~N[2019-12-22 10:58:36],
    name: "Scifi",
    slug: "scifi",
    updated_at: ~N[2019-12-22 10:58:36]
  }
]
[info] Sent 500 in 509ms
[error] #PID<0.462.0> running BlogApiWeb.Endpoint (connection #PID<0.461.0>, stream id 1) terminated
Server: localhost:4000 (http)
Request: GET /articles/new
** (exit) an exception was raised:
    ** (Protocol.UndefinedError) protocol Enumerable not implemented for %BlogApi.Categories.Category{__meta__: #Ecto.Schema.Metadata<:loaded, "categories">, articles: #Ecto.Association.NotLoaded<association :articles is not loaded>, id: 1, inserted_at: ~N[2019-12-22 10:58:36], name: "Scifi", slug: "scifi", updated_at: ~N[2019-12-22 10:58:36]} of type BlogApi.Categories.Category (a struct). This protocol is implemented for the following type(s): Ecto.Adapters.SQL.Stream, Postgrex.Stream, DBConnection.PrepareStream, DBConnection.Stream, Function, GenEvent.Stream, IO.Stream, MapSet, HashSet, HashDict, Map, List, Range, Date.Range, File.Stream, Stream
        (elixir) lib/enum.ex:1: Enumerable.impl_for!/1
        (elixir) lib/enum.ex:141: Enumerable.reduce/3
        (elixir) lib/enum.ex:3023: Enum.reduce/3
        (phoenix_html) lib/phoenix_html/form.ex:1162: Phoenix.HTML.Form.option/4
        (phoenix_html) lib/phoenix_html/form.ex:1156: anonymous fn/3 in Phoenix.HTML.Form.options_for_select/3
        (elixir) lib/enum.ex:1948: Enum."-reduce/3-lists^foldl/2-0-"/3
        (phoenix_html) lib/phoenix_html/form.ex:1112: Phoenix.HTML.Form.select/4
        (blog_api) lib/blog_api_web/templates/article/form.html.eex:26: anonymous fn/2 in BlogApiWeb.ArticleView."form.html"/1
        (phoenix_html) lib/phoenix_html/form.ex:377: Phoenix.HTML.Form.form_for/4
        (blog_api) lib/blog_api_web/templates/article/form.html.eex:1: BlogApiWeb.ArticleView."form.html"/1
        (blog_api) lib/blog_api_web/templates/article/new.html.eex:3: BlogApiWeb.ArticleView."new.html"/1
        (blog_api) lib/blog_api_web/templates/layout/app.html.eex:15: BlogApiWeb.LayoutView."app.html"/1
        (phoenix) lib/phoenix/view.ex:410: Phoenix.View.render_to_iodata/3
        (phoenix) lib/phoenix/controller.ex:729: Phoenix.Controller.__put_render__/5
        (phoenix) lib/phoenix/controller.ex:746: Phoenix.Controller.instrument_render_and_send/4
        (blog_api) lib/blog_api_web/controllers/article_controller.ex:1: BlogApiWeb.ArticleController.action/2
        (blog_api) lib/blog_api_web/controllers/article_controller.ex:1: BlogApiWeb.ArticleController.phoenix_controller_pipeline/2
        (phoenix) lib/phoenix/router.ex:288: Phoenix.Router.__call__/2
        (blog_api) lib/blog_api_web/endpoint.ex:1: BlogApiWeb.Endpoint.plug_builder_call/2
        (blog_api) lib/plug/debugger.ex:122: BlogApiWeb.Endpoint."call (overridable 3)"/2

Also i don’t understand why I get this error when i am on the articles form in the create new action

 articles: #Ecto.Association.NotLoaded<association :articles is not loaded>
def create_article(attrs \\ %{}) do
    %Article{}
    |> Article.changeset(attrs)
    |> Ecto.Changeset.cast_assoc(:category, with: &Category.changeset/2)
    |> Repo.insert()
  end

I think you want to that when you want users to create an article and a category at the same time or update an existing category at the same time. And you need to preload an empty struct or an existing record for the association following you to create a new or update an existing one.

<div class="field">
  <%= label f, :category, class: "label" %>
  <div class="control">
    <%= select f, :category_id, @categories %>
  </div>
  <p class="help is-danger"><%= error_tag f, :category %></p>
</div>

But given this part of your form I can assume that the category to associate to the new article already exists and just has to be selected. In this case you just have to update your article schema with an additional changeste like below:

def create_changeset(article, attrs) do
    article
    |> cast(attrs, [:title, :content, :published, :category_id])
    |> validate_required([:title, :content, :published, :category_id])
    |> foreign_key_constraint(:category_id, message: "Category not found!")
    |> unique_constraint(:title)
  end

So I just add one more field in the cast and validate_required functions: category_id which will be submited by the form. The foreign_key_constraint validator will throw an error if by some mean the user attempt to submit an invalid category id. So you use this changeset only to create new article unless you want to allow user to change also the category when updating an article. If you don’t want them to be able to uppdate the category of existing articles you just use your initial changeset for article update.

3 Likes

Solved in this post Problem with assoc following the phoenix doc guide by @Kurisu

1 Like

sorry folks went out for awhile, couldn’t respond. I always do this validate the required foreign keys both in rails and phoenix.