Concise way to filter on two child attributes in ArangoDB (AQL / Spring Data ArangoDB)

Question

In ArangoDB I have documents in a trip collection which is related to documents in a driver collection via edges in a tripToDriver collection, and the trip documents are also related to documents in a departure collection via edges in a departureToTrip collection.

To fetch trips where their driver has a given idNumber and their associated departure has a startTime after a supplied date/time, I've successfully written the following AQL:

FOR doc IN trip
    LET drivers = (FOR v IN 1..1 OUTBOUND doc tripToDriver RETURN v)
    LET departures = (FOR v in 1..1 INBOUND doc departureToTrip RETURN v
    FILTER drivers[0].idNumber == '999999-9999' AND departures[0].startTime >= '2018-07-30'
RETURN doc

But I wonder if there is a more concise / elegant way to achieve the same results?

A related question, since I'm using Spring Data ArangoDB: Is it possible to achieve this result with derived queries?

For a single relation I was able to create a query like: Iterable<Trip> findTripsByDriversIdNumber( String driverId ); but haven't had luck incorporating the departure relation into this signature (maybe because it's inbound?).

mpv89 · Accepted Answer · 2018-08-11T06:20:38.097

First of all your query only works if you have only one connected driver/departure for every trip. You're fetching all linked drivers but only check the first found one.

If this is your model it is totally ok, but I would recommend to do the idNumber/startTime check within the sub queries. Then, because we only need to know that at least one driver/departure fits our filter condition, we add a LIMIT 1 to the sub query and return only a true. This is enough we need for our FITLER in our main query.

FOR doc IN trip
  FILTER (FOR v IN 1..1 OUTBOUND doc tripToDriver FILTER v.idNumber == @idNumber LIMIT 1 RETURN true)[0]
  FILTER (FOR v IN 1..1 INBOUND doc departureToTrip FILTER v.startTime >= @startTime LIMIT 1 RETURN true)[0]
  RETURN doc

I tested to solve your case with a derived query. It would work, if there wasn't a bug in the current arangodb-spring-data release. The bug is already fixed but not yet released. You can already test it using a snapshot version of arangodb-spring-data (1.3.1-SNAPSHOT or 2.3.1-SNAPSHOT depending on your Spring Data version, see supported versions).

The following derived query method worked for me with the snapshot version.

Iterable<Trip> findByDriversIdNumberAndDeparturesStartTimeGreaterThanEqual(String idNumber, LocalDate startTime);

To make the dervied query work you need the following annotated fields in your class Trip

@Relations(edges = TripToDriver.class, direction = Direction.OUTBOUND, maxDepth = 1)
private Collection<Driver> drivers;

@Relations(edges = DepartureToTrip.class, direction = Direction.INBOUND, maxDepth = 1)
private Collection<Departure> departures;

I also created an working example project on github.

Thank you very much for the informative answer and example project! I opted for the derived query approach, which works with `arangodb-spring-data` `3.0.0-RC.1`. The example project also introduced me to Lombok @Data, so now I've thrown out a ton of getter/setter etc. boilerplate code :) — Bjorn Thor Jonsson, Aug 15 '18 at 18:27

Concise way to filter on two child attributes in ArangoDB (AQL / Spring Data ArangoDB)

1 Answers1