Arangodb AQL query

Question

I have data organized such way:

There are 1k of teachers, 10k of pupils, every pupil has ~100 homeworks.

I need get all homeworks of pupils, related to a teacher via classes, or by direct link between them. All vertices and edges have some attributes, and let's suppose all required indices are already built, or we can discuss them a bit later.

I can get all required pupils ids by such fast enough query:

$query1 = "FOR v1 IN 1..1 INBOUND @teacherId teacher_pupil FILTER v1.deleted == false RETURN DISTINCT v1._id";
$query2 = "FOR v2 IN 2..2 INBOUND @teacherId OUTBOUND teacher_class, INBOUND pupil_class FILTER v2.deleted == false RETURN DISTINCT v2._id";
$queryUnion = "FOR x IN UNION_DISTINCT (($query1), ($query2)) RETURN x";

Then I wrote the following:

$query = "
LET pupilIds = ($queryUnion)

FOR pupilId IN pupilIds
    LET homeworks = (
        FOR homework IN 1..1 ANY pupilId pupil_homework
            return [homework._id, pupilId]
    )  
RETURN homeworks";

I got my homeworks, and I even can try filter them, but the query is too slow - that's an incorrect way, I believe.

Question 1 How can I do it without getting all Homeworks huge amount to memory at a time (LIMIT or whatever), sorting and filtering Homeworks by vertex' attributes fast and efficient? I'm sure limiting pupils, or pupil-related homeworks in the query/subquery's FOR leads to incorrect sorting/pagination.

I did another try with pure graph AQL query:

$query1 = "FOR v1 IN 2..2 INBOUND @teacherId pupil_teacher, OUTBOUND pupil_homework RETURN v1._id";
$query2 = "FOR v2 IN 3..3 INBOUND @teacherId teacher_class, pupil_class, OUTBOUND pupil_homework RETURN v2._id";
$query = "FOR x IN UNION_DISTINCT (($query1), ($query2)) LIMIT 500, 500 RETURN x";

It isn't much faster, and I don't know how filter Teacher vertices by attributes.

Question 2 What approach is the best for building such AQL queries, how can I access vertices of a graph filtering all path's parts by attributes? Can I paginate the result to save memory and speedup the query? How can I speed up it at all?

Thank you!

Prasanna Prasanna · Accepted Answer · 2017-10-17T17:44:57

Assuming teacher and pupil are related to each other via classes(2 outbound links) or directly(single outbound link) and with no other way you can do something like this

 FOR v IN 1..2 OUTBOUND "teacher_id" GRAPH "graph_name"
   FILTER LIKE(v._id, "pupil_collection_name/%")
     FOR homeworks IN 1 OUTBOUND v GRAPH "graph_name"
        LIMIT lowerLimit,numberOfItems
           RETURN homeworks

But if there is a possibility that a teacher and pupil can be related to each other with something other than a class we would have to filter our query with respect to the edge we are seeing as well

 FOR v IN 1..2 OUTBOUND "teacher_id" GRAPH "graph_name"
   FILTER LIKE(v._id, "pupil_collection_name/%") && (e.name == "ClassPupil" || e.name == "TeacherPupil")
     FOR homeworks IN 1 OUTBOUND v GRAPH "graph_name"
        LIMIT lowerLimit,numberOfItems
           RETURN homeworks

Note that since same teacher can be related to a pupil directly as well as via a class, we can have non unique homeworks . Hence using a RETURN DISTINCT homeworks is suggested. But if duplications are not a problem, the above query should work

Arangodb AQL query

1 Answers