Has many through many fields

maliawelli · August 6, 2017, 5:51pm

Hey,

I’ve got problem with has many association. I would like to connect posts with posts_likes through “post_id” and “user_id” (which would be took from guardian Guardian.Plug.current_resource(conn)). How can I do this? It is easily possible in SQL, but how to translate it to elixir and ecto?

In pseudo code it should look like:

SELECT posts, posts_favourites WHERE post.id = posts_favourites.post_id AND posts_favourites.user_id = Guardian.Plug.current_resource(conn).id

maliawelli · August 7, 2017, 4:36am

Or if it’s not possible (but should be), I tried:

user = Guardian.Plug.current_resource(conn)
page = Post.preload
|> Repo.paginate(params)

a = Enum.reduce page.entries, %{}, fn x, entry ->
  user_id = user.id
  y = %{"favourite" => Repo.all(Post.getFavourites(x.id, user_id))}
  [y | x]
end

in model with getFavourites I download my favs for logged user. But this enum.reduce doesn’t work, could someone help me to fix this?

NobbZ · August 7, 2017, 5:36am

What do you mean by “doesn’t work”?

I do not see anything why it shouldn’t work, but probably it does “not return what you expect it to”.

user_id is static, you do not need to set it in the reduce-function, you can move it to the outside or directly use user.id
You are returning a list, consisting of a map %{"favourite" => …} as its first element and the current item as tail.
you are discarding the previous accumulator (entry) and do not use it at all
You start your accumulator as %{}, but then you make it an improper list, while this generally works it is inconsistent

So please show us some sample input (simplified), what your function returns and what you expect it to return instead.

This helps us to help you.

maliawelli · August 7, 2017, 7:31am

Thanks for your response.

Actually you are right. I do not get what I expect.

In my page.entries I’ve got something like (pseudocode):

posts => [post => [id => 1, title => 'test', 'user_id' => 2, 'comments' => ['user_id' => 2, 'post_id' => 1]]]

I would like to join to each post my favourites from %{"favourite" => Repo.all(Post.getFavourites(x.id, user_id))}

I’m coming from OOP language.
For instance in PHP it would be (again pseudocode):

for ($i = 0; $i < count(page.entries); $i++) {
page.entires[$i]['favourites'] = $this->getFavorite($post_id, $user_id);
}

Probably it is easy, but I can’t get it in elixir.

My purpose is to have information in each post if logged user have post in favourites.

NobbZ · August 7, 2017, 7:52am

This is a bad idea. Pseudocode isn’t standardized and with natural languages, everyone can interprete some things slightly different than you.

This forum is about elixir, so it is best to use elixir when writing code. But of course you can use any language you want, but please use those that do actually exist and use them sparse.

To be honest, I do not understand what that piece of pseudocode-data is. Is it a keywordlist, a map, a record, something else? Why do you have keys sometimes in quotes and sometimes not?

What I requested you for was, that you provide us input, expected output, actual output and your implementation in a simplified way, so that we can load it into iex and play around with it.

Just like this:

I have a function called inc, but it doesn’t seem to work.

My function looks like this:
def inc(x), do x - 1
I expect it to count up, but when I do inc(2) or inc(10) I do get 1 and 9 in return, while I expect it to be 3 and 11 respectively.

Can anyone help me solve this?

This example is of course overly simplified and the problem obvios, but it is just an example.

But now, lets try to help you.

As I do understand you, you have a list of things, where each thing is a map (or struct).

You want to iterate over those things and collect a list of maps with one key each. That should be roughly possible like this:

def get_favorites(posts) do
  Enum.map(posts, fn post ->
    %{"favourite" => Repo.all(Post.getFavourites(x.id, user_id))}
  end
end

But thats really only a quick guess.

Imperative for-loops which counter is used as an index in an assignment can often be translated into Enum.map/2, which makes the code much clearer.

maliawelli · August 7, 2017, 5:24pm

You understood me clearly.

Here is my data structure:

In each post I would like to have information about “favourites” (only when user is logged), like for instance I’ve got information about comments, votes etc.

I modified your code a bit:

b = cond do
   current_user ->
       Enum.map(page.entries, fn post ->
         %{"favourite" => Repo.all(Post.getFavourites(post.id, current_user.id))}
       end)
       true -> page.entries
end

IO.inspect(b)

Probably it is fine, but I’ve got error:

"an exception was raised:
          ** (Poison.EncodeError) unable to encode value: {2, 1}"

And my getFavourites:

 def getFavourites(post_id, user_id) do
        from p in "posts_favourites",
            where: p.post_id == ^post_id and p.user_id == ^user_id,
            select: {p.post_id, p.user_id}
     end

Can you help me please?

Or mayby there is better method to fetch this data?

gregvaughn · August 7, 2017, 5:55pm

What’s happening here is the select in getFavourites is returning a two element tuple with {post_id, user_id}. Poison doesn’t know how to convert tuples to json. You can get something serializable, but probably still needing to be tweaked for your needs with:

select: %{post_id: p.post_id, user_id: p.user_id}

because that will be a map that Poison can handle

NobbZ · August 7, 2017, 5:56pm

The screenshot(?) shows some arbitrary JSON(?).

In your elixir code you will probably not deal with that JSON directly, but with an represantation in elixir terms. Its much more important how those look.

maliawelli · August 7, 2017, 6:42pm

Thank you, you were right. I’ve so much to learn with elixir.

Anyway this enum.map posted above doesn’t return expected data. It returns only:
> “posts”: [ { “favourite”: }, { “favourite”: [ { “user_id”: 1, “post_id”: 2 } ] } ]

So all post data is thrown out. How can I fix this?

And mayby @gregvaughn do you have better idea to fetch this data?

@NobbZ
Yes, because I return .json data with:

|> render(“index.json”, posts: b, page: page)

Is that incorrect?

gregvaughn · August 7, 2017, 7:20pm

You’re getting the data you are querying for. The problem appears to be your getFavourites query. It doesn’t implement this (incorrect) sql you asked about.

You probably want something more like:

from p in Post,
join: f in :posts_favourites, on: f.post_id == p.id and f.user_id == ^user_id

Note: I haven’t tested that out because I don’t know your schema, but hopefully it’ll get you closer. I’m assuming you want full post data returned and not just ids like you are currently selecting

NobbZ · August 7, 2017, 7:22pm

Which is as expected according how I understood you. This is why I asked for input and output as you expect it in elixir syntax multiple times. I’m still waiting.

Nope, but irrelevant as well… After encoding json it’s just a string from elixir point of view, but we are somewhere before that, we are dealing with maps, tuples, lists, integers, floats, atoms and whatnots. Those are relevant for us.

maliawelli · August 7, 2017, 7:24pm

@gregvaughn:
I can’t use join, because I won’t get posts which are not liked by logged user. I want to have only information in each post if it is liked by user or not.

I’ve got right data, but during this operation:

Enum.map(page.entries, fn post →
%{“favourite” => Repo.all(Post.getFavourites(post.id, current_user.id))}
end)

It removes all my post data.

NobbZ · August 7, 2017, 7:29pm

Since you still didn’t show what you expect, I have to guess (again)…

Enum.map(page.entries, fn post ->
  post |> Map.put("favourite", repo.all... 
end

maliawelli · August 7, 2017, 7:32pm

@NobbZ
Ok.
I’m posting json, because it is much easier to get info than from terminal output.

I’ve got posts data like this:

“posts”: [ { “votes”: [ { “post_id”: 4, “id”: 22, “author”: “equse” } ], “user_id”: 1, “user”: { “username”: “equse”, “id”: 1 }, “url”: “http://google.pl”, “title”: “Google”, “inserted_at”: “2017-07-21T22:08:11”, “id”: 4, “comments”: [ { “updated_at”: “2017-08-05T10:56:28”, “post_id”: 4, “inserted_at”: “2017-08-05T10:51:33”, “id”: 1, “body”: “test23333”, “author”: “equse” } ] } ]

The same data in terminal output:

[%Microscope.Post{meta: ecto.Schema.Metadata<:loaded, “posts”>,
comments: [%Microscope.Comment{meta: ecto.Schema.Metadata<:loaded, “comments”>,
author: “equse”, body: “test23333”, id: 1,
inserted_at: ecto.DateTime<2017-08-05 10:51:33>,
post: ecto.Association.NotLoaded,
post_id: 4, updated_at: ecto.DateTime<2017-08-05 10:56:28>}], id: 4,
inserted_at: ecto.DateTime<2017-07-21 22:08:11>,
notifications: ecto.Association.NotLoaded,
posts_favourites: ecto.Association.NotLoaded,
title: “Google”, updated_at: ecto.DateTime<2017-07-21 22:08:11>,
url: “http://google.pl”,
user: %Microscope.User{meta: ecto.Schema.Metadata<:loaded, “users”>,
encrypted_password: “$2b$12$oWoyU3mUiVbnfqBwPPUycun3V9oHnn3kSCBoMWcW1mjcUS5C3Difa”,
id: 1, inserted_at: ecto.DateTime<2017-07-17 19:57:44>,
notifications: ecto.Association.NotLoaded,
password: nil,
posts: ecto.Association.NotLoaded,
shares: ecto.Association.NotLoaded,
updated_at: ecto.DateTime<2017-07-17 19:57:44>, username: “equse”},
user_id: 1,
votes: [%Microscope.Vote{meta: ecto.Schema.Metadata<:loaded, “votes”>,
author: “equse”, id: 22, inserted_at: ecto.DateTime<2017-08-06 12:22:14>,
post: ecto.Association.NotLoaded,
post_id: 4, updated_at: ecto.DateTime<2017-08-06 12:22:14>}]}

And it should look like:

“posts”: [ { “favourite”: [ { “user_id”: 1, “post_id”: 2 } ] , “votes”: [ { “post_id”: 4, “id”: 22, “author”: “equse” } ], “user_id”: 1, “user”: { “username”: “equse”, “id”: 1 }, “url”: “http://google.pl”, “title”: “Google”, “inserted_at”: “2017-07-21T22:08:11”, “id”: 4, “comments”: [ { “updated_at”: “2017-08-05T10:56:28”, “post_id”: 4, “inserted_at”: “2017-08-05T10:51:33”, “id”: 1, “body”: “test23333”, “author”: “equse” } ] } ]

NobbZ · August 7, 2017, 7:47pm

Which I do not know how it gets deserialized into elixir terms, therefore mostly useless, also it is invalid JSON, since it is neither a list nor a single object.

%Microscope.Post{[…]}

Oh… It is a struct. You can’t simply add or remove fields.

Is this JSON or elixir? doesn’t matter, its invalid in both cases.

This may sound hard, but it seems as if fundamentals of elixir are missing, you should pick up a book and learn about them before continuing.

Also an important advice! Try to leave behind everything you learned in PHP! OOP vs FP (and related) aren’t the only differences, but if I start now it will end with rage-bashing PHP

maliawelli · August 7, 2017, 7:48pm

@NobbZ

I’m just trying to implement “add to favourites” function. It can’t be so hard as it seems to be

Now my output looks like:

%{:__meta__ => #Ecto.Schema.Metadata<:loaded, "posts">,
  :__struct__ => Microscope.Post, :comments => [], :id => 2,
  :inserted_at => #Ecto.DateTime<2017-07-18 19:26:48>,
  :notifications => #Ecto.Association.NotLoaded<association :notifications is not loaded>,
  :posts_favourites => #Ecto.Association.NotLoaded<association :posts_favourites is not loaded>,
  :title => "Wykop", :updated_at => #Ecto.DateTime<2017-07-18 19:26:48>,
  :url => "http://wykop.pl",
  :user => %Microscope.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
   encrypted_password: "$2b$12$oWoyU3mUiVbnfqBwPPUycun3V9oHnn3kSCBoMWcW1mjcUS5C3Difa",
   id: 1, inserted_at: #Ecto.DateTime<2017-07-17 19:57:44>,
   notifications: #Ecto.Association.NotLoaded<association :notifications is not loaded>,
   password: nil,
   posts: #Ecto.Association.NotLoaded<association :posts is not loaded>,
   shares: #Ecto.Association.NotLoaded<association :shares is not loaded>,
   updated_at: #Ecto.DateTime<2017-07-17 19:57:44>, username: "equse"},
  :user_id => 1,
  :votes => [%Microscope.Vote{__meta__: #Ecto.Schema.Metadata<:loaded, "votes">,
    author: "equsek", id: 10, inserted_at: #Ecto.DateTime<2017-07-23 20:32:52>,
    post: #Ecto.Association.NotLoaded<association :post is not loaded>,
    post_id: 2, updated_at: #Ecto.DateTime<2017-07-23 20:32:52>},
   %Microscope.Vote{__meta__: #Ecto.Schema.Metadata<:loaded, "votes">,
    author: "equse", id: 23, inserted_at: #Ecto.DateTime<2017-08-06 19:47:24>,
    post: #Ecto.Association.NotLoaded<association :post is not loaded>,
    post_id: 2, updated_at: #Ecto.DateTime<2017-08-06 19:47:24>}],
  "favourite" => [%{post_id: 2, user_id: 1}]}]

So my information about favourite is outside the brackets and my API doesn’t see that data.

maliawelli · August 7, 2017, 7:50pm

@NobbZ

I’m still learning elixir and sitting with book between my knees. But this operations should be as easy as in other languages

Also I’m trying to learn during some project.

NobbZ · August 7, 2017, 7:58pm

Of what brackets?

Where do you want that information to be placed? The task itself is very simple, but only if one knows EXACTLY what you want to have!

maliawelli · August 7, 2017, 8:08pm

@NobbZ

As I wrote exactly few times. I would like to have information (if post is in favourites or not) inside each single post from posts array.

The best option would be to do this with ecto schema, but I haven’t found any example in documentation, so I’m trying just to fetch this in additional query.

NobbZ · August 7, 2017, 8:15pm

In the data you have shown us, you have shown a mangled struct, which was a Post, before you required to insert a String key.

And since Post is/should be a struct, you are normaly not allowed to simply add new keys to it. Structs are fixed. This is what I meant by saying you are missing fundamentals.

If you want to have a key in there, that is :favourites you need to add it to your defstruct or schema-definition (maybe virtual).

Also I still do not know what you mean by “outsoutside the brackets”. It would help both of us if you were using proper terms.

PS: Proper terms means as well to not talk about arrays, but lists, but I will excuse this for now.