0

I have 3 tables:

  1. categories
  2. subcategories
  3. categorySubcategories

1 category has 0 to many subcategories.

I have this function which gets all entries from categories table and only gets the entries of the subcategories table that belong to the category which has an id of 1: 

  def getCategories(conn) do
    categories = all Api.Category
    groceryItemSubcategories = from s in Api.Subcategory,
    join: cs in Api.CategorySubcategory, on: cs.c_id == 1,
    select: %{name: s.name, foo: cs.c_id}
    conn
    |> put_resp_content_type("application/json")
    |> send_resp(200, Poison.encode!(%{categories: categories, groceryItemSubcategories: groceryItemSubcategories}))
  end

giving this error:

23:10:27.169 [error] #PID<0.339.0> running Api.Router terminated
Server: localhost:4000 (http)
Request: GET /categories
** (exit) an exception was raised:
    ** (Poison.EncodeError) unable to encode value: {"subcategories", Api.Subcategory}
        (poison) lib/poison/encoder.ex:383: Poison.Encoder.Any.encode/2
        (poison) lib/poison/encoder.ex:227: anonymous fn/4 in Poison.Encoder.Map.encode/3
        (poison) lib/poison/encoder.ex:228: Poison.Encoder.Map."-encode/3-lists^foldl/2-0-"/3

        (poison) lib/poison/encoder.ex:228: Poison.Encoder.Map.encode/3
        (poison) lib/poison/encoder.ex:227: anonymous fn/4 in Poison.Encoder.Map.encode/3
        (poison) lib/poison/encoder.ex:228: Poison.Encoder.Map."-encode/3-lists^foldl/2-0-"/3

        (poison) lib/poison/encoder.ex:228: Poison.Encoder.Map.encode/3
        (poison) lib/poison.ex:41: Poison.encode!/2

The final answer is this:

  def getCategories(conn) do
    categories = all Api.Category
    groceryItemSubcategories = Api.Repo.all(from s in Api.Subcategory,
    join: cs in Api.CategorySubcategory, on: cs.s_id == s.id,
    join: c in Api.Category, on: c.id == cs.c_id,
    where: c.id == 1,
    select: %{name: s.name, foo: cs.c_id}
    )
    conn
    |> put_resp_content_type("application/json")
    |> send_resp(200, Poison.encode!(%{categories: categories, groceryItemSubcategories: groceryItemSubcategories}))
  end

The part that makes the error go away is wrapping the statement in Api.Repo.All(). Dogbert was really the one who answered it so I'm not wanting to answer this.

BeniaminoBaggins
  • 11,202
  • 41
  • 152
  • 287
  • Poison cannot encode tuples. Try selecting into a map, e.g. `select: {s.name, cs.c_id}` -> `select: %{name: s.name, foo: cs.c_id}`. – Dogbert Apr 22 '17 at 10:53
  • @Dogbert Thanks. Just posted the error i'm getting when I do that. – BeniaminoBaggins Apr 22 '17 at 10:58
  • That's a syntax error. Could you post the whole code that you tried? Probably missing a comma or something somewhere. – Dogbert Apr 22 '17 at 11:03
  • @Dogbert I just added the whole function to the bottom of the question. Let me know if you want to see more code. Cheers – BeniaminoBaggins Apr 22 '17 at 11:07
  • Oh I meant replace the code on the left of the arrow with the one on the right, not copy the whole thing. – Dogbert Apr 22 '17 at 11:08
  • @Dogbert Cheers I just edited the question after trying it again. I think the error is the same. Is it because I didn't add `@derive {Poison.Encoder, only: [:c_id, :s_id]}` to the "categorySubcategories" schema? (just tried it and didn't work though) – BeniaminoBaggins Apr 22 '17 at 11:15
  • @Dogbert I just added the schemas to show the `Poison.Encoder` commands. I think one of them is wrong. – BeniaminoBaggins Apr 22 '17 at 11:33
  • Oh wait, are you not calling `Repo.all` on the second query? – Dogbert Apr 22 '17 at 11:41
  • @Dogbert No. I just did. Thanks! It works now. It just selects the same thing a lot of times...any idea why`{"groceryItemSubcategories":[{"name":"Meat","foo":1},{"name":"Meat","foo":1},{"name":"Meat","foo":1},{"name":"Meat","foo":1},{"name":"Meat","foo":1},{"name":"Meat","foo":1},{"name":"Meat","foo":1},{"name":"Dairy","foo":1},{"name":"Dairy","foo":1},{"name":"Dairy","foo":1}` – BeniaminoBaggins Apr 22 '17 at 11:49
  • That's expected with the JOIN query you have. I don't know what output you expect, but maybe you want a `distinct: true,` before `select: ...`? – Dogbert Apr 22 '17 at 11:52
  • @Dogbert I had the select statement wrong and have now put the correct statement at the bottom of this question. It works now. You really got me most of the way so you can answer it if you wish with the solution to the initial question - wrapping the statement in `Repo.All()` is what got rid of the poison error. Otherwise I will delete the question. Thanks very much for your help. – BeniaminoBaggins Apr 22 '17 at 12:34

1 Answers1

1

There are two main issues with the original code:

  1. You forgot to call Repo.all in the second query.

  2. You're selecting a tuple in the query and then encoding it to JSON. Poison does handle encoding tuples to JSON. You can select a list or map instead, depending on what data structure you want. Here's how to select a map:

    groceryItemSubcategories = Api.Repo.all(from s in Api.Subcategory,
  join: cs in Api.CategorySubcategory, on: cs.s_id == s.id,
  join: c in Api.Category, on: c.id == cs.c_id,
  where: c.id == 1,
  select: %{name: s.name, c_id: cs.c_id})
Dogbert
  • 212,659
  • 41
  • 396
  • 397