Heyo,

I’m having a bit of trouble with more complicated order_by in my Ecto query.

• I have a Document, that has a Category assigned
• Category can have children or a parent
• I already have a function that traverses the tree and preloads children, parent or both (the preload in Ecto query goes only one level deep, for more complicated preloading I use the function)
• The children part should be irrelavant for this question

I would now like to sort/order selected Documents based on the Category and their parent and their parent etc…
Basically, to achieve that Documents are first sorted by the main category and then the following sub categories.

The code looks like this for now (Guideline == Document):

guidelines =
      from(
        g in Guideline,
        left_join: a in assoc(g, :author),
        left_join: c in assoc(g, :category),
        preload: [author: a, category: c],
        where: ^filter,
        select: g,
        # TODO: will not sort correctly when we have more than one parent category
        #
        # 1. Drafts are shown first
        # 2. Then sorted by author
        # 3. Then sorted by "main" category
        # 4. Then sorted by "secondary" category
        # 5. Then sorted by guideline (doc) ID
        #       For UX sake, don't sort by Title or Updated Time,
        #       it just shuffles the docs.
        #       Drafts are always on top, so that's
        #       good enough for editors.
        order_by: [
          desc: g.is_draft,
          asc: g.author_id,
          asc_nulls_first: c.parent_id,
          asc_nulls_first: c.id,
          asc: g.id
        ]
      )

I’m thinking I could check how many parent categories does the document has, preload them all with my function and create a order_by with multiple c.parent.id based on the count.

So the partial created order_by for 3 levels deep category would like (PS. I’m not sure whether this would work, I don’t think I can query that deep) :

      order_by =
        [
          asc_nulls_first: c.parent.parent.parent.id,
          asc_nulls_first: c.parent.parent.id,
          asc_nulls_first: c.parent.id,
          asc_nulls_first: c.id
        ]```