Sort by relation fields or even nested relation field


(James) #1

hi,
how do i sort result by relation field?
eg below:

  1. how do order by project name asc?
  2. how do i order by project.created_by.name asc, then project.name asc ?
tasks(func: has(isTask))  { 
  uid
  name
  start_at
  project {
    uid
    name
    created_by {
      uid
      name
    }
  }
}

(Michel Conrado (Support Engineer)) #2

Hi James,

well I don’t think so, the best you can do is use groupby https://docs.dgraph.io/query-language/#groupby


(Michel Conrado (Support Engineer)) #3

UPDATE: I decided to do an answer a little more elaborated after doubts appeared (in another conversation)

tasks(func: has(isTask))  { 
  uid
  name
  start_at
  project (orderasc: name) {
    uid
    name
    created_by {
      uid
      name
    }
  }
}

Indexing https://docs.dgraph.io/query-language/#indexing
eq , le , ge , lt , gt | | exact Allows faster sorting.

I believe I got an approximate result, desired, using @cascade @normalize

Using “GroupBy” is the best way to organize some relationships and get a more controlled visual result. But there are other ways to deal with it. As for example “using @cascade @normalize

There is no sorting by UID across edges. The maximum sorting per UID is the same graph level and only for paging.

Thus, it is not possible to sort by EDGES because UIDs are created randomly and never according to node values ​​(that is, values ​​like date, string and other sortable) are impossible to be predetermined by EDGES. Having said that it would be necessary to expand the Edges and then sort it.

Note Without a sort order specified, the results are sorted by uid , which is assigned randomly. So the ordering, while deterministic, might not be what you expected.
https://docs.dgraph.io/query-language/#pagination

Starts here

At the end of the post will have Mutation and Schema

{
  project(func: has(created_by), orderasc: name)@cascade @normalize {
    uid
    name
    created_by_Me: created_by (orderasc: name)
    {
        uid : uid
        project_name: name
      ~project (orderasc: name) {
       uid : uid
       task_name: name
      }
  }
  }

Result

{
  "data": {
    "project": [
      {
        "project_name": "Alice Betty Project",
        "task_name": "A: Alice TASK",
        "uid": "0xfdeda"
      },
      {
        "project_name": "Alice Betty Project",
        "task_name": "B: Alice TASK",
        "uid": "0xfdedf"
      },
      {
        "project_name": "Other Alice Betty Project",
        "task_name": "C: Alice TASK",
        "uid": "0xfdee6"
      },
      {
        "project_name": "Bob Betty Project",
        "task_name": "A: Bob TASK",
        "uid": "0xfdee7"
      },
      {
        "project_name": "Bob Betty Project",
        "task_name": "B: Bob TASK",
        "uid": "0xfdee3"
      },
      {
        "project_name": "Other Bob Betty Project",
        "task_name": "C: Bob TASK",
        "uid": "0xfdee8"
      },
      {
        "project_name": "Carlos Betty Project",
        "task_name": "A: Carlos TASK",
        "uid": "0xfdee0"
      },
      {
        "project_name": "Carlos Betty Project",
        "task_name": "B: Carlos TASK",
        "uid": "0xfdeee"
      },
      {
        "project_name": "Other Carlos Betty Project",
        "task_name": "C: Carlos TASK",
        "uid": "0xfdef1"
      },
      {
        "project_name": "Julios Betty Project",
        "task_name": "A: Julios TASK",
        "uid": "0xfdeef"
      },
      {
        "project_name": "Julios Betty Project",
        "task_name": "B: Julios TASK",
        "uid": "0xfdee9"
      },
      {
        "project_name": "Julios Betty Project",
        "task_name": "C: Julios TASK",
        "uid": "0xfdedb"
      },
      {
        "project_name": "Lucas Betty Project",
        "task_name": "A: Lucas TASK",
        "uid": "0xfdeed"
      },
      {
        "project_name": "Lucas Betty Project",
        "task_name": "B: Lucas TASK",
        "uid": "0xfded9"
      },
      {
        "project_name": "Other Lucas Betty Project",
        "task_name": "C: Lucas TASK",
        "uid": "0xfdedc"
      }
    ]
  }

Schema

<created_by>: uid @reverse .
<isTask>: bool @index(bool) .
<name>: string @index(exact) .
<project>: uid @reverse .
<start_at>: datetime @index(year) .

Mutation

{
  "set": [
    {
      "uid": "_:alice",
      "name": "Alice",
      "created_by": [
        {
          "name": "Alice Betty Project",
          "uid": "_:AliceBetty"
        },
        {
          "name": "Other Alice Betty Project",
          "uid": "_:OtherAliceBetty"
        }
      ]
    },
    {
      "uid": "_:Bob",
      "name": "Bob",
      "created_by": [
        {
          "name": "Bob Betty Project",
          "uid": "_:BobBetty"
        },
        {
          "name": "Other Bob Betty Project",
          "uid": "_:OtherBobBetty"
        }
      ]
    },
    {
      "uid": "_:Lucas",
      "name": "Lucas",
      "created_by": [
        {
          "name": "Lucas Betty Project",
          "uid": "_:LucasBetty"
        },
        {
          "name": "Other Lucas Betty Project",
          "uid": "_:OtherLucasBetty"
        }
      ]
    },
    {
      "uid": "_:Carlos",
      "name": "Carlos",
      "created_by": [
        {
          "name": "Carlos Betty Project",
          "uid": "_:CarlosBetty"
        },
        {
          "name": "Other Carlos Betty Project",
          "uid": "_:OtherCarlosBetty"
        }
      ]
    },
    {
      "uid": "_:Julios",
      "name": "Julios",
      "created_by": {
        "name": "Julios Betty Project",
        "uid": "_:JuliosBetty"
      }
    },
    {
      "name": "A: Alice TASK",
      "isTask": "True",
      "start_at": "2018",
      "project": {
        "uid": "_:AliceBetty"
      }
    },
    {
      "name": "B: Alice TASK",
      "isTask": "True",
      "start_at": "2018",
      "project": {
        "uid": "_:AliceBetty"
      }
    },
    {
      "name": "C: Alice TASK",
      "isTask": "True",
      "start_at": "2018",
      "project": {
        "uid": "_:OtherAliceBetty"
      }
    },
    {
      "name": "A: Bob TASK",
      "isTask": "True",
      "start_at": "2018",
      "project": {
        "uid": "_:BobBetty"
      }
    },
    {
      "name": "B: Bob TASK",
      "isTask": "True",
      "start_at": "2018",
      "project": {
        "uid": "_:BobBetty"
      }
    },
    {
      "name": "C: Bob TASK",
      "isTask": "True",
      "start_at": "2018",
      "project": {
        "uid": "_:OtherBobBetty"
      }
    },
    {
      "name": "A: Lucas TASK",
      "isTask": "True",
      "start_at": "2018",
      "project": {
        "uid": "_:LucasBetty"
      }
    },
    {
      "name": "B: Lucas TASK",
      "isTask": "True",
      "start_at": "2018",
      "project": {
        "uid": "_:LucasBetty"
      }
    },
    {
      "name": "C: Lucas TASK",
      "isTask": "True",
      "start_at": "2018",
      "project": {
        "uid": "_:OtherLucasBetty"
      }
    },
    {
      "name": "A: Carlos TASK",
      "isTask": "True",
      "start_at": "2018",
      "project": {
        "uid": "_:CarlosBetty"
      }
    },
    {
      "name": "B: Carlos TASK",
      "isTask": "True",
      "start_at": "2018",
      "project": {
        "uid": "_:CarlosBetty"
      }
    },
    {
      "name": "C: Carlos TASK",
      "isTask": "True",
      "start_at": "2018",
      "project": {
        "uid": "_:OtherCarlosBetty"
      }
    },
    {
      "name": "A: Julios TASK",
      "isTask": "True",
      "start_at": "2018",
      "project": {
        "uid": "_:JuliosBetty"
      }
    },
    {
      "name": "B: Julios TASK",
      "isTask": "True",
      "start_at": "2018",
      "project": {
        "uid": "_:JuliosBetty"
      }
    },
    {
      "name": "C: Julios TASK",
      "isTask": "True",
      "start_at": "2018",
      "project": {
        "uid": "_:JuliosBetty"
      }
    }
  ]
}

Cheers, Happy New year!


#4

Can I use pagination with @normalize? Something like: give me first 10 tasks sorted by name.


(Michel Conrado (Support Engineer)) #5

Yes, you can.

Cheers


#6

Hmm, but pagination is working on has(created_by) but I would like to paginate results. My schema is little different and I have more project on one task.


(Michel Conrado (Support Engineer)) #7

Not sure what you mean. Pagination works with any set of nodes. Normalize just flats/squash the result.