Ecto query order for preload

Questions / Help
,
1

Hey, I have this query:

recording_query =
  from(
    r in Recording,
    order_by: [desc: r.id],
    where: r.user_id == ^user.id,
    preload: [:user, comment: [:user]],
    select: r
  )

Repo.all(recording_query)

How can I order the preloaded data? the comments here?

2

figured it myself:

comment_order_query = from(c in Comment, order_by: c.inserted_at, preload: [:user])

recording_query =
  from(
    r in Recording,
    order_by: [desc: r.id],
    where: r.user_id == ^user.id,
    preload: [:user, comment: ^comment_order_query],
    select: r
  )

Repo.all(recording_query)
6 Likes
3

Is there a way to get recording’s comment ONLY instead of preloading a list of all?

4

I think you can join the comment and then select that.

5

Hi thank you for the response,

to add on, i am retrieving a list of items that preload their children. In my case, how can I achieve that?

e.g. I am getting a list of recordings with comments and i want to sort comment (for example) by title ascending order

6

e.g.


from(
  r in query,
  # join: c in assoc(r, :comments),
  join: rc in assoc(, :recording_comments),
  preload: [
    comments: from(t2 in Comment,
      where: t2.id == rc.comment_id, # I think this pointing is what i miss, but how can i achieve this?
    order_by: t2.inserted_at, preload: [:tag_group])
  ],
  order_by: [asc: rc.title]
)
7

I have tried something like this guy do: Preload with order_by from another table with Ecto

but the order is fucked and some data is missing. so i am very confused

8

I don’t think you can do preload ordering like this.
https://hexdocs.pm/ecto/Ecto.Query.html#preload/3-preload-queries

What type of relation do you have here?

With this you are applying ordering to r.
You need to select the comments in your query.
https://hexdocs.pm/ecto/Ecto.Query.html#select/3
select: c or rc depending on what is what.

9

I have a files that has a middle table of file_tags and tags. So the relationship is really straightforward:
1 files can have multiple file_tags, file_tags has FKs of files and tags

10

Update:

I have a files that has a middle table of file_tags and tags.

For instance:
1 file can have multiple file_tags, file_tags has FKs of files and tags to form a relationship like: files ← file_tags → tags

I am trying to get the tags of the files preloaded by doing:

from(f in File, as: :f)
|> ...
|> join(:left, [f: f], ft in FileTag, on: ft.file_id == f.id, as: :ft)
|> join(:left, [ft: ft], t in Tag, on: t.id == ft.tag_id, as: :t)
|> join(:inner, [t: t], tg in TagGroup, on: tg.id == t.tag_group_id, as: :tg)
|> preload([t: t, tg: tg], tags: {t, tag_group: tg})
|> order_by([ft: ft, f: f], asc: ft.inserted_at, desc: f.updated_at)
|> Repo.all()

I managed to order it but the number of files become 1 right now.

i believe my query is fine and i cant figure out what’s wrong … :skull:

Thanks in advance.

Best wishes,
Jing Hui P.

11

Hmm, any chance there’s only one file that has a tag belonging to a tag group? If so, that inner join would return only that one file.

If you want all the files returned regardless of whether their tags belong to a tag group, try switching to a left join.

1 Like
12

No, i have a list of files that linked to many tags.

I FOUND A BETTER SOLUTION!

I believe this should be the approach, i just need to declare this in my schema:

 many_to_many(:tags, Tag, join_through: FileTag, preload_order: [asc: :inserted_at])

BUT, i actually want the inserted_at of the FileTag NOT the File, something like this:

many_to_many(:tags, Tag, join_through: FileTag, preload_order: [asc: ^ft.inserted_at])

Reference: Support `order_by` in associations · Issue #3509 · elixir-ecto/ecto · GitHub

13

I think instead of many_to_many you could do:

    has_many :file_tags, FileTag, preload_order: [asc: :inserted_at]
    has_many :tags, through: [:file_tags, :tag]

and then preload the :tags in your query.

https://hexdocs.pm/ecto/Ecto.Schema.html#has_many/3-has_many-has_one-through

4 Likes
14

Thank you SOOOOOOOOOOOOOOOOOOO MUCH!!! <3

IT WORKED!

1 Like
15

HIi @benonymus,

after introducing this change, it breaks my put_assoc, is there any practice to follow to make my put_assoc work?

because currently i am getting this error:

[error] GenServer #PID<0.810.0> terminating
** (ArgumentError) cannot put assoc `tags`, assoc `tags` not found. Make sure it is spelled correctly and that the association type is not read-only

Thanks in advance.

16

That is expected. The last line from the docs I linked: Ecto.Schema — Ecto v3.10.2

Note :through associations are read-only. For example, you cannot use Ecto.Changeset.cast_assoc/3 to modify through associations.

1 Like
17

Thank you so much for the pointer!

@benonymus Thank you.

For those who might try to research similar topics as me, this is my workaround:

has_many(:file_tags, FileTag, preload_order: [asc: :id])
has_many(:sorted_tags, through: [:file_tags, :tag]) # I use this when i need to display

many_to_many(:tags, Tag, join_through: FileTag) # This for default behaviour, such as put_assoc

Hope it helps

1 Like