Query works in iex, but having trouble showing query result in a page

As you’ll soon notice, I’m very new to Phoenix and Elixir, and I’m not really a dev (more background at the bottom of this post.) So, prepare to laugh at my expense, but if you’ve got some pointers, I’d appreciate it.

I’ve been reading official docs, tutorials, stackexchange threads, and threads here, but I’ve still been stuck on this for a couple of days. It seems like I must be missing something obvious, because this task seems like it must be simple. There seem to be multiple ways to do what I’m trying to do, and I’ve tried several, but I’m still flailing, so…

I’m working with an existing DB from an old Ruby version of the site. The DB contains all the monologues from all of Shakespeare’s plays.

Since it’s an existing DB, and I want to get the site back online asap, when I got the static stuff in place and was ready to start writing queries , I started with this tutorial: Breaking Out of Ecto Schemas , which might be how/why I’ve painted myself into this corner…

I’ve got a route with a variable for playid. I’ve got a page displaying all titles of all the plays. When a play title is clicked on, it sends the playid to a controller with a join query in it (details from two different tables), and pulls up a list of all the monologues for that play in a different page/view. It also displays some details from some other cells (one of those values includes the title of the play.) That’s working fine. (If I’m not describing that well, you can see how it used to work in the Ruby version of the site, via the Wayback Machine if you have patience for how slowly pages load there. Follow this link, wait for it to load, then click on any play title, and you’ll see what I described previously in this current paragraph: Monologues in Shakespeare )

I’m stuck at trying to display the title of that play at the top of the page, above the list of monologues (in an <h1>.) So far, I can get the title be repeated multiple times, e.g. If the link clicked was a play with 34 monologues, I can get it to display the title 34 times, above the list of the monologues. I do understand why that’s happening (and you will too, shortly), but I can’t figure out the correct way to do it so that it displays the title in an <h1> only once.

So, some details:

Erlang/OTP 24 
Elixir 1.12.2 
Phoenix 1.6.15

deps
      {:phoenix, "~> 1.6.15"},
      {:phoenix_ecto, "~> 4.4"},
      {:ecto_sql, "~> 3.6"},
      {:postgrex, ">= 0.0.0"},
      {:phoenix_html, "~> 3.0"},
      {:phoenix_live_reload, "~> 1.2", only: :dev},
      {:phoenix_live_view, "~> 0.17.5"},
      {:floki, ">= 0.30.0", only: :test},
      {:phoenix_live_dashboard, "~> 0.6"},
      {:esbuild, "~> 0.4", runtime: Mix.env() == :dev},
      {:swoosh, "~> 1.3"},
      {:telemetry_metrics, "~> 0.6"},
      {:telemetry_poller, "~> 1.0"},
      {:gettext, "~> 0.18"},
      {:jason, "~> 1.2"},
      {:plug_cowboy, "~> 2.5"},
      {:html_assertion, "0.1.5", only: :test},
      {:credo, "~> 1.6", only: [:dev, :test], runtime: false}

From lib/mono_phoenix_v01_web/router.ex, this is the route for the links with the play ids:

get("/play/:playid", PlayPageController, :play)

Here are the contents of that controller. (The first query I can use, the other isn’t currently working. One question I’ve been unable to find an answer to is: “Is it valid to have two queries in the same controller?”):

lib/mono_phoenix_v01_web/controllers/play_page_controller.ex

defmodule MonoPhoenixV01Web.PlayPageController do
  use MonoPhoenixV01Web, :controller
  import Ecto.Query

  # Show monologues for the play link clicked
  @spec play(Plug.Conn.t(), map) :: Plug.Conn.t()
  def play(conn, params) do
    playid = String.to_integer(params["playid"])

    query =
      from(m in "monologues",
        join: p in "plays",
        on: m.play_id == p.id,
        where: p.id == ^playid,
        group_by: [p.id, p.title, m.character, m.first_line, m.location, m.body],
        select: %{
          play: p.title,
          character: m.character,
          firstline: m.first_line,
          location: m.location,
          body: m.body
        }
      )

    rows = MonoPhoenixV01.Repo.all(query)

    render(conn, "play.html", rows: rows)
  end

  # Show the title of the play
  @spec title(Plug.Conn.t(), map) :: Plug.Conn.t()
  def title(conn, params) do
    playid = String.to_integer(params["playid"])

    query =
      from(p0 in MonoPhoenixV01.Play,
        where: p0.id == ^playid,
        select: [:id, :title]
      )

    titles = MonoPhoenixV01.Repo.one(query)

    render(conn, "plays.html", title: titles)
  end
end

Here’s the version of the template which does the repeating of the title (row.play):

lib/mono_phoenix_v01_web/templates/play_page/play.html.heex

# this section causes the title ('row.play') to be repeated as described earlier in the post:
<section class="play">
  <%= for row <- @rows do %>
    <h1><%= row.play %></h1>
  <% end %>  
</section>

# this section works fine.  probably not the best approach, but it works for now
<section class="row">
  <article class="monologue_preview">
     <%= for row <- @rows do %>
       <br><br>
       <%= row.play %>, <%= row.character %>, <%= row.firstline %>, <%= row.location %>
      <br>
       <%= raw(row.body) %>
     <% end %>
  </article>
</section>

(If you’re currently asking yourself “Why did he do it that way?” The answer is: Because I’m a clueless n00b. :blush: )

I’ve tried every other relevant word I can think of to use instead of rows (and in the 2nd part of the controller), such as “columns”, “titles”, “plays” etc etc. All lead to Compilation errors, which makes me think “rows” is defined somewhere in default files, but I’ve been unable to find info about how I’d add my own.

So, I tried to correct my schema-less path, by creating a schema for the play titles:

lib/mono_phoenix_v01/play.ex

defmodule MonoPhoenixV01.Play do
  @moduledoc """
  This module provides the schema for the title from the Plays table. Or would If I knew what I'm doing.
  """
  use Ecto.Schema

  schema "titles" do
    field(:title, :string)
    field(:classification, :string)
  end
end

I can query that schema in iex:


 iex(11)> query = from(MonoPhoenixV01.Play, where: [id: ^"9"], select: [:id, :title])
#Ecto.Query<from p0 in MonoPhoenixV01.Play, where: p0.id == ^"9",
 select: [:id, :title]>

iex(12)> MonoPhoenixV01.Repo.all(query)
[debug] QUERY OK source="plays" db=1.0ms idle=1521.7ms
SELECT p0."id", p0."title" FROM "plays" AS p0 WHERE (p0."id" = $1) [9]
↳ :erl_eval.do_apply/6, at: erl_eval.erl:685
[
  %MonoPhoenixV01.Play{
    __meta__: #Ecto.Schema.Metadata<:loaded, "plays">,
    classification: nil,
    id: 9,
    title: "All's Well That Ends Well"
  }
]

iex(13)>

So I tried changing the first section in lib/mono_phoenix_v01_web/templates/play_page/play.html.heex to this:

<section class="play">
  <%= for title <- @titles do %>
    <h1><%= title.title %></h1>
  <% end %>  
</section>

Which gives me:

KeyError at GET /play/9
key :titles not found in: %{conn: %Plug.Conn{adapter: {Plug.Cowboy.Conn, :...}, assigns: %{layout: {MonoPhoenixV01Web.LayoutView, "app.html"}, rows: [%{body: "If ever we are nature's, these are ours; this thorn ...

etc.

Also tried: <h1><%= row.title %></h1>

But got:

== Compilation error in file lib/mono_phoenix_v01_web/views/play_page_view.ex ==
** (CompileError) lib/mono_phoenix_v01_web/templates/play_page/play.html.heex:4: undefined function row/0
    (elixir 1.12.2) src/elixir_locals.erl:114: anonymous fn/3 in :elixir_locals.ensure_no_undefined_local/3
    (stdlib 3.17.2.2) erl_eval.erl:685: :erl_eval.do_apply/6
    (elixir 1.12.2) lib/kernel/parallel_compiler.ex:319: anonymous fn/4 in Kernel.ParallelCompiler.spawn_workers/7

“Help me ObiWan ElixirForum. You’re my only hope.”

=-=-=-=-=-= Boring backstory, and why I’m so clueless atm =-=-=-=-=-=-=
I first built the site in plain ol’ html back in 1997. later moved it to php.

In 2010 a good friend asked if he could rebuild the site in Ruby On Rails, to beef up his skills and resume. A couple of years after that, we moved it from Rails to Padrino to cut down on maintenance time. We were using Ruby v2.3.0. Then both of our lives got much busier. The site always worked, rarely needed anything changed, an ad revenue arrived monthly. I didn’t think about the site much for several years. I do not like Ruby, so I never felt like trying to change things unless I had to.

Fast forward to December 1, 2022. Heroku stop supporting a few things, and the site went down. It took a few weeks, but I realized that trying to upgrade the site from Padrino and Ruby 2.3.0 to Sinatra and Ruby 3.1.3 was a fools errand, due to the age of the gems in the site, and a massive myriad of incompatible things on the upgrade path, and, well, the general state of Ruby these days.

Then I setup Phoenix and Elixir on my local and it only took me a week to make the same progress that took 6 weeks in Ruby. Working on the site is fun again.

I was clipping along at a good pace until I hit the wall of my lack of knowledge and got bleary-eyed reading docs and tutorials and trying things to figure out how to display the title once at the top of the play/:playid page. Hoping some kind soul will see this post and help me over my current hurdle. Thanks.

in play_page_controller.ex here

rows = MonoPhoenixV01.Repo.all(query)
#                       here 👇🏻
render(conn, "play.html", rows: rows)

you are assign data to :rows key… so in the view it’s available via @rows
if you did

#                          👇🏻
render(conn, "play.html", titles: rows)

then @title in the view would work.

1 Like

Apart from the suggestion above, I would suggest (in general):

  • make functions to encapsulate the logic (behavior) and have controllers call them (policy). So you can test them and exclude you have issues there
  • Logger is your best friend. Twice so when learning.
1 Like

Thanks much, I’ll give that another try.

What do you mean by " then @title in the view would work." ? I think not knowing how to do that is why I couldn’t get render(conn, "play.html", titles: rows) to work when I tried it initially.

Sounds like that’s probably wiser, if I knew what you meant. I’m rushing to learn phoenix and elixir to revive a site that is down, and I’m still learning concepts and structures (have read through many of the docs, from request life-cycle forward, but reading it isn’t the same as doing it), so I’m still thinking in terms of which things to put in which file. I’m still at a point where I need file names and code examples, because I’m still learning the language, but I understand that not everyone has the time or will to answer in that kind of detail.

1 Like

“Simple” (less moving parts) != “easy” (straightforward to use). I’d recommend starting with “standard” Ecto schemas and leave the optimization angle for later.

The local variable titles here doesn’t escape the controller function; the view gets an assign named @title (no s!)


(added a linebreak to show the important part) The error here is telling you that the controller is still passing rows but you’re asking for titles in the template.

1 Like

Thanks very much for the reply, @al2o3cr . Bear with me. As I mentioned, I’m clueless and very new to this, so my questions below are probably not questions that an actual Elixir dev would ask…

“Simple” (less moving parts) != “easy” (straightforward to use). I’d recommend starting with “standard” Ecto schemas and leave the optimization angle for later.

Yes, I’m starting to see that the choice try to go schema-less to save time was probably not as helpful as I thought (it’s not an optimization, was just a clueless attempt to save time.)

The local variable titles here doesn’t escape the controller function; the view gets an assign named @title (no s!)

Where you said “no s!” did you mean “It lacks an s!” or did you mean “remove the s!” Also, which instance of title/titles are you referring to? Iow, did you mean I should change it to this:

    title = MonoPhoenixV01.Repo.one(query)

    render(conn, "plays.html", title: titles)

or I should change it to this?:

    title = MonoPhoenixV01.Repo.one(query)

    render(conn, "plays.html", titles: titles)

or this?

    titles = MonoPhoenixV01.Repo.one(query)

    render(conn, "plays.html", titles: title)

or this?

    titles = MonoPhoenixV01.Repo.one(query)

    render(conn, "plays.html", titles: titles)

I’ve tried all four of those. Some of the errors are related to rows vs titles, which you also mentioned.

The error here is telling you that the controller is still passing rows but you’re asking for titles in the template.

Yes, I mentioned in my op that I don’t understand that, cluenessess again. Is it valid to define the two separate queries in the same controller file, or could that be part of the trouble? How do I get the controller to pass titles for the titles, while still passing rows for the rows?

Here’s what I’m having trouble understanding in regard to rows vs titles…

This works for the ‘rows’: In ... web/controllers/play_page_controller.ex, I have this under the query for the def play which lists all the monologues with the matching playid:

    rows = MonoPhoenixV01.Repo.all(query)

    render(conn, "play.html", rows: rows)

Then in the 2nd section of ... web/templates/play_page/play.html.heex, I have this, and it works:

<section class="row">
  <article class="monologue_preview">
     <%= for row <- @rows do %>
       <br><br>
       <%= row.play %>, <%= row.character %>, <%= row.firstline %>, <%= row.location %>
      <br>
       <%= raw(row.body) %>
     <% end %>
  </article>
</section>

But when I do the same thing with titles it fails:

In ... web/controllers/play_page_controller.ex I have this under the query for the def showtitle (changed from def title in my op, to def showtitle, to avoid confusion with title):

    titles = MonoPhoenixV01.Repo.one(query)

    render(conn, "plays.html", titles: titles)

Then in the first section of ... web/templates/play_page/play.html.heex, I have this:

<section class="title">
  <article class="show_title">
    <%= for title <- @titles do %>
      <h1><%= title.title %></h1>
    <% end %>  
  </article>
</section>

But that gives me this error:

[info] GET /play/9
[debug] Processing with MonoPhoenixV01Web.PlayPageController.play/2
  Parameters: %{"playid" => "9"}
  Pipelines: [:browser]
[debug] QUERY OK source="monologues" db=1.4ms idle=918.0ms
SELECT p1."title", m0."character", m0."first_line", m0."location", m0."body" FROM "monologues" AS m0 INNER JOIN "plays" AS p1 ON m0."play_id" = p1."id" WHERE (p1."id" = $1) GROUP BY p1."id", p1."title", m0."character", m0."first_line", m0."location", m0."body" [9]
↳ MonoPhoenixV01Web.PlayPageController.play/2, at: lib/mono_phoenix_v01_web/controllers/play_page_controller.ex:25
[info] Sent 500 in 17ms
[error] #PID<0.919.0> running MonoPhoenixV01Web.Endpoint (connection #PID<0.913.0>, stream id 2) terminated
Server: localhost:4000 (http)
Request: GET /play/9
** (exit) an exception was raised:
    ** (KeyError) key :titles not found in: %{conn: %Plug.Conn{adapter: {Plug.Cowboy.Conn, :...}, assigns: %{layout: {MonoPhoenixV01Web.LayoutView, "app.html"}, rows: [%{body: "If ever we are nature's, these are ours; 

What I’m not understanding is why rows works but titles doesn’t, even though they appear (to my clueless eye) to be configured identically.

What can I do to get titles working like the rows do?

Thanks.

My interpretation of your post is that you’ve got a controller that looks something like:

defmodule MonoPhoenixV01Web.PlayPageController do
  use MonoPhoenixV01Web, :controller
  import Ecto.Query

  # Show monologues for the play link clicked
  @spec play(Plug.Conn.t(), map) :: Plug.Conn.t()
  def play(conn, params) do
    # yadda yadda yadda

    render(conn, "plays.html", rows: rows)
  end

  def showtitle(conn, params) do
   # yadda yadda yadda

    titles = MonoPhoenixV01.Repo.one(query)

    render(conn, "plays.html", titles: titles)
  end
end

Adding code in showtitle won’t do anything because it isn’t being run! Some important clues from the error message:

  • Processing with MonoPhoenixV01Web.PlayPageController.play/2
  • MonoPhoenixV01Web.PlayPageController.play/2, at: lib/mono_phoenix_v01_web/controllers/play_page_controller.ex:25
  • %Plug.Conn{adapter: {Plug.Cowboy.Conn, :...}, assigns: %{..., rows: [%{body:

I strongly recommend thinking over randomly spamming changes. Assigns aren’t the most straightforward thing in the world, but the first two versions are wrong because of basic rules like “use the same name when reading and writing a local variable”. Here’s the same code with distinct names:

    # in the controller
    a_local_variable = MonoPhoenixV01.Repo.one(query)

    render(conn, "plays.html", an_assign: a_local_variable)

    # in the view
    <%= @an_assign.whatever %>

One final note: looping over the single-struct result from Repo.one with for will likely not do what you want.

2 Likes

Remove the for list comprehension so that you’re not displaying the title 34 times. Instead, use hd/1 to take the first row and its play title attribute.

<section class="play">
  <h1><%= hd(@rows).play %></h1>
</section>

Some general pointers, the naming here is pretty confusing and setting up a proper schema like others have pointed out would likely be very helpful. And since you’ve worked with Rails, it’s important to note that there’s very little pluralization magic in Phoenix so unless you explicitly set the plural or singular, you won’t have access to it. For example,

None of these would work except for the last one because:

foo = MonoPhoenixV01.Repo.one(query)
# ^ ------- has to match ------- v
render(conn, "plays.html", bar: foo)
#                     v -- ^ and these two as well
# which would make @bar available in the view

Also, it’s generally not a good idea/practice to assign a pluralized variable name to the result of a Repo.one and may be why you’re confused. The more straightforward way that you haven’t tried would be:

title = MonoPhoenixV01.Repo.one(query)
render(conn, "plays.html", title: title)
# which would make @title available in the view

That’s because there’s a crucial difference between Repo.one and Repo.all where the former returns one element and the latter returns multiple meaning you can use the for list comprehension to “loop” through the latter, but not the former.

1 Like

Thanks for the follow-up @al2o3cr .

That’s half of what’s in the controller. Which is why I have been asking if it is valid to have both in the same controller. Here’s the full content of ...web/controllers/play_page_controller.ex again (note that it has two separate defs with two different queries in it):

defmodule MonoPhoenixV01Web.PlayPageController do
  use MonoPhoenixV01Web, :controller
  import Ecto.Query

  # Show monologues for the play link clicked
  @spec play(Plug.Conn.t(), map) :: Plug.Conn.t()
  def play(conn, params) do
    playid = String.to_integer(params["playid"])

    query =
      from(m in "monologues",
        join: p in "plays",
        on: m.play_id == p.id,
        where: p.id == ^playid,
        group_by: [p.id, p.title, m.character, m.first_line, m.location, m.body],
        select: %{
          play: p.title,
          character: m.character,
          firstline: m.first_line,
          location: m.location,
          body: m.body
        }
      )

    rows = MonoPhoenixV01.Repo.all(query)

    render(conn, "play.html", rows: rows)
  end

  # Show the title of the play
  @spec showtitle(Plug.Conn.t(), map) :: Plug.Conn.t()
  def showtitle(conn, params) do
    playid = String.to_integer(params["playid"])

    query =
      from(p0 in MonoPhoenixV01.Play,
        where: p0.id == ^playid,
        select: [:id, :title]
      )

    title = MonoPhoenixV01.Repo.one(query)
    render(conn, "plays.html", title: title)
  end
end

Adding code in showtitle won’t do anything because it isn’t being run!

Yes, as I mentioned, I just changed it to reduce confusion in the names. I know it’s not being run, that’s why I keep asking if it’s valid to have two queries in the same controller.

Yes, those clues do look important. But I’m still too clueless to understand them, which is why I posted here.


Not sure what you mean. I posted those because I was seeking clarification about your ambiguous statement in your initial reply:

The local variable titles here doesn’t escape the controller function; the view gets an assign named @title (no s!)

Above the list that you thought was ‘randomly spamming changes’, I prefaced it with this:

Where you said “no s !” did you mean “It lacks an s !” or did you mean “remove the s !” Also, which instance of title /titles are you referring to? Iow, did you mean I should change it to this:

Followed by the list of possible things you may have meant.

Could you please clarify <%= @an_assign.whatever %>? I’m pretty sure that my confusion over the many ways to call things in views is where I’m screwing it up (which leads me to try to change things in the controller, even though the problem is probably what I’m doing in the view (...web/templates/play_page/play.html.heex)


I’m not trying to loop over the Repo.one. Repo.one is in the 2nd query in the controller (def showtitle). I’m trying to display the title only once at the top of the page. Is Repo.one not correct for trying to display it only once?

The Repo.all is there to bring in all monologues with a matching playid. Repo.all is used in the first query in the controller (def play), so it is supposed to loop. I’m trying to figure out how to keep this one looping, while showing the other only once.

I used for in the 1st section in the view only because it’s the only thing I’ve tried that I can get to display the title at all, but yes, it loops. I’m asking about it here, hoping someone will tell me what to put in the view to call what’s in the controller (and to find out if I have the controller and queries right or not.) As I’ve mentioned, I’m quite clueless.

That’s why I keep describing what I’m thinking and trying to do, with each block of code I post. Because if you only read my code, thinking I know what I’m doing, you’ll make incorrect assumptions about what I’m trying to do (because I don’t know how to do what I’m trying to do, which is why I’m here. :slight_smile: )

Hi @codeanpeace , Thanks for the reply and pointers, you got me where I needed to go! :tada::metal::slightly_smiling_face:

(for those in the future who may happen upon this thread while searching for resolution for a similar problem, the codeblock in this next quote is what fixed it for me!)

Aha! I am purposefully displaying all of the monologues with a matching playid. I’m trying to get the title of the play to display above the monologues, just once. Note the two separate sections in the view (play.html.heex.) The 1st section is for the title, which I want to display only once. The 2nd section is for the multiple monologues that match the playid. That 2nd section is working correctly. (The controller has two different defs in it, with two different queries, one for the monologues, one for the title)

But I was able to interpret your example above to change that first section to this:

 <section class="title">
  <article class="show_title">
  <h1><%= hd(@rows).play %></h1>
  </article>
</section>

And it works! Thank you so much! I don’t recall seeing hd in any of the docs or tutorials I’ve read (I must have missed it somehow.)

Excellent tip, thanks. I’ve worked with Rails, but I suck at Ruby. A friend wrote the site in Ruby for me, then I was stuck trying to maintain it, and I still do not get along well with it after 12 years, which is why I’ve finally switched to Phoenix and Elixir ftw.

Yes, I only added the 2nd query to the controller when I didn’t know about hd(@rows). Now I know I was right when I was thinking I didn’t need an extra query, I needed to know how to call the title once in the view.

Thank you so much! I’m now unblocked and moving ahead. You have lived up to your name, you brought peace to my code. :slight_smile: w00t!

Hey, glad you got it working!

Before you move on, I think you should listen to your gut here! What you wanted to accomplish is fairly cookie cutter so I highly encourage you to spend some time properly setting up Ecto Schemas. It would likely have saved you a ton of headache and continue to pay dividends moving forward.

For example, once you set up those Ecto schemas…

defmodule MonoPhoenixV01.Play do
  use Ecto.Schema

  schema "plays" do
    field(:title, :string)
    field(:classification, :string)
    has_many :monologues, MonoPhoenixV01.Monologue
  end
end
defmodule MonoPhoenixV01.Monologue do
  use Ecto.Schema

  schema "monologues" do
    field(:character, :string)
    field(:first_line, :string)
    field(:location, :string)
    field(:body, :string)
    belongs_to :plays, MonoPhoenixV01.Play
  end
end

… you can refactor and drastically simplify the controller action.

alias MonoPhoenixV01.{Repo, Play}

def play(conn, params) do
    # you can get the play and its associated monologues very easily
    play = Play |> preload(:monologues) |> Repo.get(params["playid"])

    # then just add the play to the assigns
    render(conn, "play.html", play: play)
    # or alternatively, assign the play's title and play's monologues
    render(conn, "play.html", title: play.title, monologues: play.monologues)
end

And now in the view, you’ll have access to them via @play.title and @play.monologues for the first option or @title and @monologues for the second option. That way there’s no need to pull the play title off of one of the monologues.

<section class="play">
    <h1><%= @play.title %></h1>
       <!--  or -->
    <h1><%= @title %></h1>
</section>

<section class="row">
  <article class="monologue_preview">
     <%= for monologue <- @play.monologues do %>
         <!--  or -->
     <%= for monologue <- @monologues do %>
       <br><br>
       <%= @play.title || @title %>, <%= monologue.character %>, <%= monologue.first_line %>, <%= monologue.location %>
      <br>
       <%= raw(monologue.body) %>
     <% end %>
  </article>
</section>
1 Like

Yes, that’s definitely the direction I’m heading after this thread. :slight_smile:

Thanks so much for taking the time to write up those examples for me @codeanpeace ! I very much appreciate it, you saved me a chunk of time in figuring how to to get there!

I’m also creating controllers to show men’s and women’s monologues for a play (with separate index pages to click through from) so, thanks to you, I now know how to quickly get those controllers and views set up. :slight_smile: