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

Skip to first unread message

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)

in post_controller.ex

which uses:

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

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

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

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: == p.authorid,
select: {, p.description, p.complete, p.title, p.body, p.inserted_at, p.updated_at, p.authorid,,, u.familyname, u.firstname}

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)

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

Can someone help me before I give up on

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