Ecto - Composing a recursive order_by

Questions / Help
, , , ,
1

:wave: 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
        ]```
2

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.

I might misunderstand, but it looks like you are trying to sort your documents, correct? If so then you will be sorting over entires that potentially have different numbers of parent categories. i.e. c.parent.parent.parent.id might not exist for some documents.

Some strategies I can think of, without knowing your use case too well:

  • Pre-compute the category hierarchy for each document and save it in a separate field. Then you can order by a single column. There is extra overhead in maintaining this field whenever the categories change, but for your use case this might be more efficient than computing and sorting the hierarchy every time the information is fetched.
  • Change your UI so that it is only showing the category that is assigned to the document and then give them the ability to view the hierarchy for a single document when they click on something.
1 Like
3

Yep, correct! The “potentially different number” should be guarded by my traverse/preload function, where I enumerate how many parent categories does the doc have and based on that number I create the maximal c.parent.parent.parent… I was thinking a macro or something.

I really like the UI change. I kept thinking from backend perspective, but you’re definitely correct that sorting based on more than 2 categories has minor benefit.