How to create a json api output from a inner join query

59 views
Skip to first unread message

j....@rlo-gelo.nl

unread,
Nov 22, 2017, 4:50:19 PM11/22/17
to phoenix-talk
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", postanduserspostandusers)
  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 

benwil...@gmail.com

unread,
Nov 23, 2017, 11:38:05 AM11/23/17
to phoenix-talk
This mailing list has been deprecated, I suggest looking at https://elixirforum.com/
Reply all
Reply to author
Forward
0 new messages