ArangoDB - Sort result in aql with custom function

Question

I'd like to have sorted result depending on user input.

Lets say I have sort object that could look like this:

var sort = {createdAt: -1}

or like this:

var sort = {createdAt: 1, name: 1}

And I have query that looks like this:

FOR f in [{createdAt: 123, name: 'BBB'},{createdAt: 2000, name: 'ZZZ'}, {createdAt: 2000, name: 'BBB'}]
    SORT f.createdAt DESC
    RETURN f

and it works ok. But I'd like to SORT result by field that is passed in sort object by user. I added custom arango function:

db.createFunction(
    'CUSTOM::FILTERING::SORT_STRING',
            String(function (sort, it) {
                    return sort && Object.keys(sort).length !== 0 && sort.constructor === Object ? Object.keys(sort).map(key => `${it}.${key} ${sort[key] >= 0 ? 'ASC' : 'DESC'}`).join(', ') : '';
            })
        );

but when I'm using it that way it doesn't work at all. Result is not sorted in any way:

FOR f in [{createdAt: 123, name: 'BBB'},{createdAt: 2000, name: 'ZZZ'}, {createdAt: 2000, name: 'BBB'}]
    SORT CUSTOM::FILTERING::SORT_STRING(${sort}, 'f')
    RETURN f

How can I sort result basing on different input arguments?

Have you had a look at Foxx Microservices within ArangoDB? This is a perfect application of them, let Foxx present a REST API and then the user can provide Sort, PageNum, PageSize, Query attributes and the Foxx REST API will do it for you. — David Thomas, Aug 12 '17 at 06:06
@DavidThomas could you give me some example of implementing it? — MatiK, Aug 14 '17 at 07:11
Have a look at [this answer](https://stackoverflow.com/questions/42427063/sending-http-post-request-from-node-to-foxx-service-arangodb/42451340#42451340) where I provided an example of how to set up a Foxx microservice to respond to a REST API request. You can allow the caller to provide additional query params either through the path, query string, or body, and then have your code call appropriate queries. How to write the Foxx Microservice is beyond the scope of this question, but it follows a Node.js style format and there are lots of examples online, especially in github.com. — David Thomas, Aug 14 '17 at 09:22
@DavidThomas Unfortunately Foxx is not an option for me. Is there no way to do it with custom function? — MatiK, Aug 18 '17 at 07:30
They are moving away from UDF's, with preference for Foxx. Sorry to hear Foxx can't work for you, I can't answer your question in a pure custom function point of view. I use Foxx Microservices to do this. — David Thomas, Aug 18 '17 at 08:29
@DavidThomas And I guess there is no way to achieve this with pure aql? — MatiK, Aug 18 '17 at 11:16

score 1 · Answer 1 · answered Aug 19 '17 at 13:26

1

Pure AQL, you can do this but somewhere in the world a puppy will die...

RETURN (@sortBy == 'createdAt' ?
  (FOR d IN @@collectionName
    SORT createdAt DESC
    RETURN d) : (@sortBy == 'name' ?
      (FOR d in @@collectionname
        SORT name DESC
        RETURN d)
    )
  )
)

But the other way is to dynamically generate the AQL, with proper code checking you can do it safely.

I sometimes generate AQL dynamically, but all parameters are carefully scanned, cleansed, Joi schema proofed, and validated to stop SQL injection.

The other way of doing this style query is:

LET sortByCreatedAt = (
  FOR d in @@collectionName
    SORT createdAt DESC
    RETURN d)

LET sortByName = (
  FOR d in @@collectionName
    SORT name DESC
    RETURN d)

RETURN (@sortBy == 'createdAt') ? sortByCreatedAt : sortByName

This isn't pretty, but works, and with creativity you can write heavily nested and complex queries with ASC and DESC as options, as well as a predefined number of column names. Importantly the column names can't by fully dynamic, but can be user selected.

I haven't tested these on an ArangoDB server so some typo's may exist.

answered Aug 19 '17 at 13:26

David Thomas

2,264
2
18
20

I can't do it not-dynamically. I sometimes need to sort by name and createdAt field, sometimes only by createdAt. There is too much permutations to write each case separately. I'd like also to reuse it with other queries. I can write function that creates such string dynamically but then I cannot use js aql tag when calling db query. It's a shame there is no easy way to do sorting with arango :( – MatiK Aug 21 '17 at 12:31
Sorting is easy.. is there a reason why you can't use Foxx or dynamically generate your AQL before you send it? – David Thomas Aug 22 '17 at 13:05
I can't use Foxx because of clients restrictions. And dynamically generated AQL is not working with js aql tag. It fails because of security restrictions. I don't want to be vunerable to injections. If I do validation myself there is always risk that I'll miss sth important. – MatiK Aug 22 '17 at 16:09
Have you tried something like `for d in @@collection sort d.@mysort @direction return d`? @mysort is your key to sort on, @direction is 'asc' or 'desc' – David Thomas Aug 23 '17 at 11:10
Yas. And it works if there is fixed number of fields to sort on. But if I have different number of fields to sort and with different directions it is not working :( – MatiK Aug 23 '17 at 12:34
I think your problem is the constraints. If you were working with other database technology, e.g. MSSQL, and had the constraint that you can't use Stored Procs, and you can't dynamically write SQL queries, you would have the same problem. I'd recommend talking to the client about the benefits of Foxx. One key advantage is you stop your clients from performing AQL queries on your DB, rather you expose REST API endpoints and that's how they get data. You can then inject business logic and schema obfuscation, providing a rich data service rather than a DB Query service – David Thomas Aug 25 '17 at 06:24

ArangoDB - Sort result in aql with custom function

1 Answers1