CODEWITHSUNDEEP
spring-dataarangodbaql
Bjorn Thor Jonsson
Bjorn Thor Jonsson

Reputation: 827

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

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?).

Upvotes: 0

Views: 305

Answers (1)

mpv89
mpv89

Reputation: 1891

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.

Upvotes: 1

Related Questions