Ecto Preload with Condition from the Main Query

Questions / Help
, ,
1

Hi guys just wondering is it possible to preload but with a condition from the main query

Let’s say I have a model named Post. A Post has a tag which is a string. Then I want to query a specific Post for example Post with id = idA. While querying the Post I also want to preload all other Post which have a similar tag under a field called similar_posts in the query. So it looks something like this:

 %Post{
      id: "ID_A",
      tag: "TopicA",
      similar_posts: [
        %Post{
          id: "ID_B",
          tag: "topicA"
        },
        %Post{
          id: "ID_C",
          tag: "TopicA"
        }
      ]
    }

Let’s say I already created an association in the Post model:

has_many :similar_posts, MyApp.Post

When I tried

from(p in Post,
    preload: [similar_posts: ^from(pp in Post, 
         where: pp.id != p.id and
                pp.tag == p.tag, 
         order_by: pp.inserted_at
    )]
)

It error said that p is undefined. Can someone help me? Am I doing something wrong here or is it not possible and better to seperate the query itself after retrieving the result.

2

If a post can have many “similar_posts” does it mean that a post belongs to another post?
It seems to me that you can’t have a has_many association without a belongs_to association in the other way. I guess when preloading children, a belongs_to association should be required.

What I would do is to make a simple querry that returns all the similar posts and merge it into a virtual field defined in the Post schema.

You can easily do that with select_merge.

Also it would be preferable to add a limit to the number of similar posts…

3

That’s not actually a requirement in ecto. Using the though option it can also be used for many to many relationships for example. It doesn’t necessitate an actual foreign key relationship on the db level.

If you don’t join the to be preloaded posts on the root query then the preload will happen in a separate query, which then of course doesn’t have access to p anymore. You can look at preload functions to build your query or as already mentioned join things on your root query.

1 Like