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

[j....@rlo-gelo.nl]

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 

[benwil...@gmail.com]

This mailing list has been deprecated, I suggest looking at https://elixirforum.com/