[db] The 'order_by' clause is not taking effect.

[pinghe]

[
  {
    "SelectValues": {
      "keys": [],
      "ids": {
        "Search": {
          "algorithm": "BreadthFirst",
          "conditions": [],
          "origin": {
            "Alias": "root"
          },
          "destination": {
            "Id": 0
          },
          "limit": 0,
          "offset": 0,
          "order_by": [          {
            "Asc": {
              "String": "id"
            }
          }]
        }
      }
    }
  }
]

[michaelvlach]

Hi, this might be a misunderstanding. The id here refers to a user property, not an internal database id. There is at present no way to order the elements by the database id which is on purpose because it does not cary any meaningful value. Most importantly it is NOT sequential. I.e. you cannot rely on the fact that the next inserted element would have greater id than the previous one. Ids are reused from deleted elements for instance.

There are few ways you can achieve something similar. Some ideas:

• Select elements with breadth first and without ordering. They will be naturally from newest to oldest (no need for id if that is the only important thing).
• You could also apply an id as a property by inspecting how many connected edges there are to root: QueryBuilder.select().edge_count_from().ids("root").query(). Although if you also delete elements this might not be reliable.
• Implement the counter as a property on root and read & increment it on insert of new node as part of the same transaction.
• Inspect id of last inserted node: QueryBuilder.search().from("root").where().node().and().keys(["id"]).query() that will yield newest element. Althoguh if you also delete elements this might not be reliable.

Anyway in case you meant actual property id I have tested this query and it appears to be working. Queries I have run:

QueryBuilder.insert().nodes().aliases("root").query(),
QueryBuilder.insert()
    .nodes()
    .values([[["id", 5]], [["id", 3]], [["id", 7]]])
    .query(),
QueryBuilder.insert().edges().from("root").to(":1").query(),
QueryBuilder.select()
    .ids(
        QueryBuilder.search()
            .from("root")
            .order_by(DbKeyOrder.Asc("id"))
            .query(),
    )
    .query(),

and the result of the last query was:

{"result":7,"elements":[{"id":3,"from":null,"to":null,"values":[{"key":{"String":"id"},"value":{"I64":3}}]},{"id":2,"from":null,"to":null,"values":[{"key":{"String":"id"},"value":{"I64":5}}]},{"id":4,"from":null,"to":null,"values":[{"key":{"String":"id"},"value":{"I64":7}}]},{"id":1,"from":null,"to":null,"values":[]},{"id":-7,"from":1,"to":4,"values":[]},{"id":-6,"from":1,"to":3,"values":[]},{"id":-5,"from":1,"to":2,"values":[]}]}

When I flipped the order_by to Desc I got:

{"result":7,"elements":[{"id":4,"from":null,"to":null,"values":[{"key":{"String":"id"},"value":{"I64":7}}]},{"id":2,"from":null,"to":null,"values":[{"key":{"String":"id"},"value":{"I64":5}}]},{"id":3,"from":null,"to":null,"values":[{"key":{"String":"id"},"value":{"I64":3}}]},{"id":1,"from":null,"to":null,"values":[]},{"id":-7,"from":1,"to":4,"values":[]},{"id":-6,"from":1,"to":3,"values":[]},{"id":-5,"from":1,"to":2,"values":[]}]}

Both are correct. The elements with an id property are first and correctly ordered and the rest of the elements withou follows in unchanged order.

[pinghe]

Understood, I was using the internal database ID for sorting, which seems to be an incorrect approach. Sorting by other fields works normally. Thank you!

[pinghe]

Additionally, I have another question. How can I avoid duplicate insertion of edges in the code QueryBuilder.insert().edges().from("root").to(":1").query(),?

[michaelvlach]

That would be the use case for the ids() option in the query builder that facilitates insert-or-update:

// This will insert new node only if "root" does not exist yet
QueryBuilder.insert().nodes().aliases("root").query()

// This will insert 3 new nodes only when the search query in ids does not yield any ids
QueryBuilder.insert()
    .nodes()
    .ids(
        QueryBuilder.search()
            .from("root")
            .where()
            .node()
            .and()
            .not()
            .ids("root")
            .query(),
    )
    .count(3)
    .query()

// This will insert edges only when there are not any yet
QueryBuilder.insert()
    .edges()
    .ids(QueryBuilder.search().from("root").where().edge().query())
    .from("root")
    .to(":1")
    .query()

I have tested these queries and they are re-entrant meaning they won't insert duplicates when run repeatedly. Of course you would likely want to fine tune the conditions etc. I have also found a bug in that server does not replace ":1" with previous query when this alias is found in the insert ids. Eg. insert().nodes().ids(":1"). Opened #1192 for it.