Associative Schemas, References, Foreign Keys

Today we are going to recreate the basic functionality of Wordpress. The idea is that we can have router paths such as

/topics/:slug             Shows a list of pages for the given topic and allows us to edit/add/delete topics
/topics/:slug/:page_id    Shows us the page content. Has a back button to return to the given topic

Actual page links could be

/topics/sport
/topics/sport/1
/topics/coding/2

And upon visiting them we will display completed pages with relevant content. We will further have a CMS (Content Management System) in place that will help us manage those resources.

Here is a screenshot of the page add form to get a sense of what a select form will be.

Schema and Generators

Let's start by using a new variant of the Phoenix generator to create our CRUD operations via a live view system opposed to controllers. Lets first peek at the intended database schema:

Let's first discuss this schema:

• Pages will belong to a topic via a topic_id foreign key and require a topic to be selected.
• Pages will have time stamps (inserted_at, updated_at) so we can potentially sort them by date.
• Topics will have a slug field that needs to be unique (so that we don't have url conflicts)

It is now time to discuss with your partner what schemas we should logically create first. Hopefully you agree that we want to start at tables that do not have any dependencies:

mix phx.gen.live Content Topic topics slug:string:unique title:string

Notice the new feature for our generators:

• Appending :unique to a field will create a unique index ensuring that no two rows can have the same slug.

You should definately check out the section on Generators if you haven't already. There are amazing options that can do really advanced stuff for us. The best part is that this also adapts all the auto-generated tests and the more fine-graned you specify the generator the better the tests will account for all eventualities.

5 minutes spend reading this section equals 5+ hours less work doing it ourselves.

Now before we migrate we will want to remove the timestamps() lines from both the migration as well as the schema as the topic table doesn't need them.

# migration file:
timestamps(type: :utc_datetime) # remove this line

# schema file:
timestamps(type: :utc_datetime) # remove this line

If you would like to enforce database constraints such as null: false now would be the time to upgrade your migrations. This is technically not needed as we can enforce required fields via changesets but remember what i told you about database constraints in the video.

Now you are ready migrate and add the routes to your router that are suggested. For the first generator you should see recommendations like this in the output:

Add the live routes to your browser scope in lib/app_web/router.ex:

    live "/topics", TopicLive.Index, :index
    live "/topics/new", TopicLive.Index, :new
    live "/topics/:id/edit", TopicLive.Index, :edit

    live "/topics/:id", TopicLive.Show, :show
    live "/topics/:id/show/edit", TopicLive.Show, :edit


Remember to update your repository by running migrations:

    $ mix ecto.migrate

You could consider protecting the /new and /edit routes by placing them into an appropriate scope that came with the authentication system but again this is not strictly necessary.

URL Friendiness (2 point)

When doing web development we strive for userfriendly URLs. That usually means replacing things such as IDs with slugs. Thus we want to really replace these routes:

live "/topics/:id", TopicLive.Index, :edit
live "/topics/:id/edit", TopicLive.Index, :edit

with

live "/topics/:slug", TopicLive.Index, :edit
live "/topics/:slug/edit", TopicLive.Index, :edit

It is your job to adapt the page and modify the code in multiple files (router, tests, live views, templates) correctly look topics up by slug rather than id and change links and tests thorughout your app accordingly.

You can do this now or skip this step for now and come back to it later.

Finishing Pages and Rerouting (2 point)

Please test the creation and deletion of topics though your live view pages thoroughly. Once everything seems to work we are ready to tackle our second generator:

mix phx.gen.live Content Page pages content:text topic_id:references:topics

Notice another interesting addition here. topic_id:references:topics We can directly create a foreign key and index. Unfortunately this does not extend to the schema and we will need to do some manual work here to complete the schema:

defmodule App.Content.Page do
  use Ecto.Schema
  import Ecto.Changeset

  schema "pages" do
    field :content, :string
    field :topic_id, :id # <- we will need a belongs_to relation here.

    timestamps(type: :utc_datetime)
  end

  @doc false
  def changeset(page, attrs) do
    page
    |> cast(attrs, [:content])
    |> validate_required([:content])
  end
end

Your tasks:

• correctly implement a belongs_to relation to the topics table
• cast topic_id, make it a required field and add a foreign_key_constraint
• add topic_id to your page as an input field in your form so we can provide it.
• (optional) add some topics and pages via seed file to streamline the process.
• adapt the route/live view to have the page show action served under:

/topics/:slug/:page_id

instead of:

/pages/:page_id

You will earn these points if you can successfully create pages after making these changes

Select Menu (Topics) in Page form. (2 points)

Modern web forms would not expect a user to know and enter the id of a topic. Instead we see fancy select boxes that provide a list of options among we can select one:

Modify your page form to use a select menu instead of an text input field. For this objective you should load all topics as a list of tuples in the format:

[
  {title, id},
  ...
]

and assign it to @topic_options. The best place to do this would be in the mount() function but you will find that page_live/show.html doesn't have one. Thus we will need to create it.

You can then use a select field:

<.input type="select" field={f[:input_id]} options={@topic_options} />

Tests (1 point)

All generator created tests must still pass after any changes you make to your website for this assignments. This means that you will have to modify some tests manually. Do not simply delete them.