Json object from inner join on two tables how to?

Phoenix Forum Questions / Help
1

Hi all,
I am a newbie in Phoenix from Holland trying to get a json result from an inner join on two tables.
Seems to me an easy task, but I get pretty frustrated not getting a simple task like this to work.

I have a table users and a table posts. The posts table is created with “mix phx.gen.json Message Post posts …”.
From this api I am able to get a results table in json using

def index(conn, _params) do
  posts = Message.list_posts()
  render(conn, "index.json", posts: posts)
end

in post_controller.ex

which uses:

def render("index.json", %{posts: posts}) do
  %{data: render_many(posts, PostView, "post.json")}
end
  
def render("index.json", %{postsandusers: postsandusers}) do
  %{data: render_many(postsandusers, PostView, "postsanduser.json")}
end

def render("show.json", %{post: post}) do
  %{data: render_one(post, PostView, "post.json")}
end

def render("post.json", %{post: post}) do
  %{id: post.id,
    description: post.description,
    complete: post.complete,
    title: post.title,
    body: post.body,
    authorid: post.authorid}
end

from post_view.ex

Now I created the following function in message.ex

def list_postandusers do
  query = from p in Post,
    join: u in Mijnsite.User, where: u.id == p.authorid,
    select: {p.id, p.description, p.complete, p.title, p.body, p.inserted_at, p.updated_at, p.authorid, u.id, u.email, u.familyname, u.firstname}
				
  Repo.all(query)
end

This function is ok.

But how can I render result in a json object and sent this using Ajax to my http client

I expected to do something like:

def index(conn, _params) do
  postandusers = Message.list_postandusers()
  render(conn, "index.json", postandusers: postandusers)
end

I tried to place it at various places, but all in vain: in post_controller.ex , add a Postanduser controller and view taking Post controller and view as an example.
At this moment I added the following lines to router.ex

scope "/api", MijnsiteWeb do
  pipe_through :api
     
  resources "/posts", PostController, except: [:new, :edit]
  get "/postandusers", PostanduserController, :index
  get "/postandusers/:id", PostanduserController, :show
end

Can someone help me before I give up on

2

It is hard to answer this question because the error is not mentionned.

What do You see when You go to

  • localhost:4000/api/posts
  • localhost:4000/api/postandusers
  • localhost:4000/api/postandusers/1

Also, You are using only one view… PostView for two controllers

PostController
PostanduserController

3

Dear kokolegorille,

Thank you very much for responding.
I hope you can help me out and that you can provide a general approach.

The first attempt I made is given below, having a different controller did not change much:

Main error: Could not render “showandu.json” for MijnsiteWeb.PostView (or show.json in case of Postanduser controller)

   scope "/api", MijnsiteWeb do
     pipe_through :api
     
	 resources "/posts", PostController, except: [:new, :edit]
	 get "/postandusers", PostController, :indexandu
	 get "/postandusers/:id", PostController, :showandu
   end

In post_controller I added:

      def indexandu(conn, _params) do
        postandusers = Message.list_postandusers()
        render(conn, "indexandu.json", postandusers: postandusers)
      end

      def showandu(conn, %{"id" => id}) do
        postanduser = Message.get_postanduser!(id)
        render(conn, "showandu.json", postanduser: postanduser)
      end

In post_view.ex I added:

      def render("indexandu.json", %{postsandusers: postsandusers}) do
        %{data: render_many(postsandusers, PostView, "postsanduser.json")}
      end

      def render("postsanduser.json", %{postsandusers: postsandusers}) do
      %{  id: postsandusers.id,
          description: postsandusers.description,
          complete: postsandusers.complete,
          title: postsandusers.title,
          body: postsandusers.body,
          authorid: postsandusers.authorid}
      end
      
      def render("showandu.json", %{postsanduser: postsanduser}) do
        %{data: render_one(postsanduser, PostView, "postsanduser.json")}
      end

The functions in message.ex are:

      def list_postandusers do
    	query = from p in Post,
    				join: u in Mijnsite.User, where: u.id == p.authorid,
    				select: {p.id, p.description, p.complete, p.title, p.body, p.inserted_at, p.updated_at, p.authorid, u.id, u.email, u.familyname, u.firstname}
    				
        Repo.all(query)
      end
     
      def get_postanduser!(postid) do  #: Repo.get!(Postanduser, id)
    	query = from p in Post,
    				join: u in Mijnsite.User, where: u.id == p.authorid
        # Extend the query
        query2 = from [p, u] in query, 
    			select: {p.id, p.description, p.complete, p.title, p.body, p.inserted_at, p.updated_at, p.authorid, u.id, u.email, u.familyname, u.firstname},
    			where: p.id==^postid
        Repo.all(query2)
      end

Running http://localhost:4000/api/posts/63 gives:

{"data":{"title":"Ikdoe","id":63,"description":"geen","complete":true,"body":"Lichaam","authorid":1}}`

while running http://localhost:4000/api/postandusers/63 gives:

Could not render "showandu.json" for MijnsiteWeb.PostView, please define a matching clause for render/2 or define a template at "lib/mijnsite_web/templates/post". No templates were compiled for this module.Assigns:`

%{conn: %Plug.Conn{adapter: {Plug.Adapters.Cowboy.Conn, :...}, assigns: %{layout: false, `postanduser: [{63, "geen", true, "Ikdoe", "Lichaam", ~N[2017-11-21 21:45:23.543000], ~N[2017-11-21 21:45:23.543000], 1, 1, "j.val@hccnet.nl", "Val", "John"}]`}, before_send: [#Function<1.66631281/1 in Plug.Logger.call/2>, #Function<0.83265233/1 in Phoenix.LiveReloader.before_send_inject_reloader/2>], body_params: %{}, cookies: %Plug.Conn.Unfetched{aspect: :cookies}, halted: false, host: "localhost", method: "GET", owner: #PID<0.1493.0>, params: %{"id" => "63"}, path_info: ["api", "postandusers", "63"], path_params: %{"id" => "63"}, peer: {{127, 0, 0, 1}, 60334}, port: 4000, private: %{MijnsiteWeb.Router => {[], %{}}, :phoenix_action => :showandu, :phoenix_controller => MijnsiteWeb.PostController, :phoenix_endpoint => MijnsiteWeb.Endpoint, :phoenix_format => "json", :phoenix_layout => {MijnsiteWeb.LayoutView, :app}, :phoenix_pipelines => [:api], :phoenix_router => MijnsiteWeb.Router, :phoenix_template => "showandu.json", :phoenix_view => MijnsiteWeb.PostView, :plug_session_fetch => #Function<1.112984571/1 in Plug.Session.fetch_session/1>}, query_params: %{}, query_string: "", remote_ip: {127, 0, 0, 1}, req_cookies: %Plug.Conn.Unfetched{aspect: :cookies}, req_headers: [{"host", "localhost:4000"}, {"connection", "keep-alive"}, {"user-agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/62.0.3202.94 Safari/537.36"}, {"upgrade-insecure-requests", "1"}, {"accept", "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8"}, {"accept-encoding", "gzip, deflate, br"}, {"accept-language", "nl-NL,nl;q=0.9,en-US;q=0.8,en;q=0.7"}], request_path: "/api/postandusers/63", resp_body: nil, resp_cookies: %{}, resp_headers: [{"cache-control", "max-age=0, private, must-revalidate"}, {"x-request-id", "06pjp8clvu77e6a5p74dvuo95ihoj611"}], scheme: :http, script_name: [], secret_key_base: "EFD0NXPVGy0Cj2QBJkrtXXR1fd+llFOQtARk/VAA6ob4JHMk0ANnYOk2WtYY0Ltd", state: :unset, status: nil}, postanduser: [{63, "geen", true, "Ikdoe", "Lichaam", ~N[2017-11-21 21:45:23.543000], ~N[2017-11-21 21:45:23.543000], 1, 1, "j.val@hccnet.nl", "Val", "John"}], template_not_found: MijnsiteWeb.PostView, view_module: MijnsiteWeb.PostView, view_template: "showandu.json"}

Assigned keys: [:conn, :postanduser, :template_not_found, :view_module, :view_template]
1 Like
4

Because of this error… I would suggest to debug that by adding a catch-all clause. Just to see the failing args. Place this below the original one, in the post view.

  def render("showandu.json", args) do
    IO.inspect args
  end
1 Like
5

postsanduser in the function head while the assign is postanduser (plural posts in the first, singular post in the latter)

1 Like
6

Dear NobbZ and Kokolegorille.
First a thanks to NobbZ for finding the typo, but that did not help much. The suggestion by Kokolegorille helped me in finding a major difference in the Repo responses

in the controller I put

  def showandu(conn, %{"id" => id}) do
    postanduser = Message.get_postanduser!(id)
    IO.write( "Starting debugging  postandusers controller \n\r")
	IO.inspect postanduser
	IO.write( "End debugging postandusers controller   \n\r" )
    render(conn, "showandu.json", postanduser: postanduser)
  end

  def show(conn, %{"id" => id}) do
    post = Message.get_post!(id)
    IO.write( "Starting debugging  posts controller \n\r")
	IO.inspect post
	IO.write( "End debugging posts controller   \n\r" )
    render(conn, "show.json", post: post)
  end

Now inspect postanduser results in:

[{63, "geen", true, "Ikdoe", "Lichaam", ~N[2017-11-21 21:45:23.543000],
  ~N[2017-11-21 21:45:23.543000], 1, 1, "j.val@hccnet.nl", "Val", "John"}]

while inspect post results in:

%Mijnsite.Message.Post{__meta__: #Ecto.Schema.Metadata<:loaded, "posts">,
 authorid: 1, body: "Lichaam", complete: true, description: "geen", id: 63,
 inserted_at: ~N[2017-11-21 21:45:23.543000], title: "Ikdoe",
 updated_at: ~N[2017-11-21 21:45:23.543000]}

postanduser was the ruslt of the function
def get_postanduser!(postid) do #: Repo.get!(Postanduser, id)
query = from p in Post,
join: u in Mijnsite.User, where: u.id == p.authorid
query2 = from [p, u] in query,
select: {p.id, p.description, p.complete, p.title, p.body, p.inserted_at, p.updated_at, p.authorid, u.id, u.email, u.familyname, u.firstname},
where: p.id==^postid
Repo.all(query2)
end

and post the result from
def get_post!(id), do: Repo.get!(Post, id)

So postanduser is a list containing a tuple while the second is a json structure.

So I have to make my own json structure or is there a more elagant way?
Thanks a lot.
John

1 Like
7

Dear NobbZ and Kokolegorille,

At this stage I made a postanduser_controller.ex and postanduser_view.ex with the content which does the job.
Thanks for putting me on the right track. I believe I learned a lot.
John

defmodule MijnsiteWeb.PostanduserController do
  use MijnsiteWeb, :controller
# view for query 
# 	query = from p in Post,
#				join: u in Mijnsite.User, where: u.id == p.authorid,
#				select: {p.id, p.description, p.complete, p.title, p.body, p.inserted_at, p.updated_at, p.authorid, u.email, u.familyname, u.firstname}
# multiple select https://medium.com/@McElaney/so-lets-say-we-have-a-system-that-tracks-vehicles-d2c05309328d
# https://hexdocs.pm/ecto/Ecto.Query.html#content

  alias Mijnsite.Message
  
  action_fallback MijnsiteWeb.FallbackController

  def indexandu(conn, _params) do
    postandusers = Message.list_postandusers()
    IO.write( "Starting debugging  postandusers controller \n\r")
	IO.inspect postandusers
	IO.write( "End debugging postandusers controller   \n\r" )
    render(conn, "indexandu.json", postandusers: postandusers)
  end

  def showandu(conn, %{"id" => id}) do
    postanduser = Message.get_postanduser!(id)
    render(conn, "showandu.json", postanduser: postanduser)
  end

end

and

defmodule MijnsiteWeb.PostanduserView do
  use MijnsiteWeb, :view
  alias MijnsiteWeb.PostanduserView
# view for query 
# 	query = from p in Post,
#				join: u in Mijnsite.User, where: u.id == p.authorid,
#				select: {p.id, p.description, p.complete, p.title, p.body, p.inserted_at, p.updated_at, p.authorid, u.email, u.familyname, u.firstname}


  def render("indexandu.json", %{postandusers: postandusers}) do
#   IO.write( "Starting debugging  postandusers view \n\r")
#	IO.inspect postandusers
#	IO.write( "End debugging postandusers view   \n\r" )
    %{data: render_many(postandusers, PostanduserView, "postanduser.json")}
  end

  def render("showandu.json", %{postanduser: postanduser}) do
    [ h | t ] = postanduser
    %{data: render_one(h, PostanduserView, "postanduser.json")}
  end
  
  
  def render("postanduser.json", %{postanduser: postanduser}) do
#   IO.write( "Starting debugging  postanduser.json view \n\r")
#	IO.inspect postanduser
#	IO.write( "End debugging postanduser.json view   \n\r" )
	%{ 	id: elem(postanduser,0), 
		description: elem(postanduser,1),
		complete: elem(postanduser,2),
		title: elem(postanduser,3),
		body: elem(postanduser,4),
		inserted_at: elem(postanduser,5),
		updated_at: elem(postanduser,6),
		autpostanduserorid: elem(postanduser,7),
		email: elem(postanduser,8),
		familyname: elem(postanduser,9),
		firstname: elem(postanduser,10)
	}
  end
 
end
1 Like