How to filter documents on the properties of their linked documents?

how can I drill down a couple levels like this in the following select

Map(
  Paginate(
Filter(Match(Index("allUsers")), ref =>
  ContainsStr(Select(["data", "locations", "data", "locationName"], Get(ref)), "Some Location")
)
  ),
  ref => Get(ref)
)

for extra context there’s locations: [Location!]! @relation
on the User document and users: [User!]! @relation on the Location document.

I’m trying to query for all users that belong to a specific Location via the locationName

type User {
  locations: [Location!]! @relation
  fullName: String
  createdAt: Date
  updatedAt: Date
}
type Location {
  locationName: String!
  users: [User!]! @relation
  createdAt: Date
  updatedAt: Date
}

sample mutation

mutation createUser($payload: UserInput!) {
  createUser(data: $payload) {
    _id
    fullName
    locations {
      data {
        locationName
      }
    }
  }
}

where payload is

{
   "payload":{
      "fullName":"kim possible",
      "locations":{
      "create": {
          "locationName": "some place"
        }
      }
   }
}

Hi,

what you are trying to do is already quite advanced so let’s break it down. A small advice when writing filters, the easiest way to write a Filter is often to start with a Map and then convert it to a filter. With the map, you can see what each step of your query returns so you can more easily construct it incrementally.

First things first, with the partial schema, FaunaDB should have generated something like that:
Collections:
image
Indexes
(I did add the allUsers index) to the schema myself as a query since the partial schema you provided will not give you that and I wanted to use the same index as you used in your example.
image

Then, try to think as if you are thinking to do this in a regular programming language such as JavaScript.

We have a location ‘some place’ and users have multiple locations. We want to find all users that have this location. In a regular programming language we would (or could):

  • Get all users
  • Apply a filter over the users
  • In that filter, get all locations
  • loop over the locations to check whether one of those locations matches the search term.

Before we can start, let’s get users. You already have an index generated by your schema I saw called allUsers.

 Paginate(Match(Index('allUsers')))

That index only returns references since the values of that index only had ‘ref’ in values.
Therefore, we need to get the user itself. As mentioned, we’ll start with a Map.

Map(
  Paginate(Match(Index('allUsers'))),
  Lambda(
    ['userref'],
    Get(Var('userref'))
  )
)

This will get us all complete user documents with their data.
Let’s restructure that though and introduce Let which will help us to structure the query.

Map(
  Paginate(Match(Index('allUsers'))),
  Lambda(
    ['userref'],
    Let(
      {
        user: Get(Var('userref'))
      },
      Var('user')
    )
  )
)

Let let’s us assign variables (in this case ‘user’) and each of these variables can be used in the next statement. So let’s continue building (we’ll zoom in on the Let in the code sample), we can get the locations by using another generated index (location_users_by_user). We select the ‘data’ immediately here to make it easier to work with later on. This is where it helps a lot to run a partial query like that and realize what the structure of the data is that each part returns.

...
    Let(
      {
        user: Get(Var('userref')),
        locationrefs: Select(['data'], Paginate(Match(Index('location_users_by_user'), Var('userref')))),
      },
      Var('locationrefs')
    )
...

Then we can continue and go from location references to the actual documents (we’ve done that before on the user, using Get). Another tip, you can always return multiple values from that let in an object to see how the data in each step of that let looks like, which we’ll do here:

...
    Let(
      {
        user: Get(Var('userref')),
        locationrefs: Select(['data'], Paginate(Match(Index('location_users_by_user'), Var('userref')))),
        locations: Map(Var('locationrefs'), Lambda(['lr'], Select(['data'], Get(Var('lr')))))
      },
      { user: Var('user'), locationrefs: Var('locationrefs'), locations: Var('locations') }
    )
...

If you run the complete query then you could see that this works so far and see how the resulting structure of each of the objects returned like:

{
  data: [
    {
      user: {
        ref: Ref(Collection("User"), "268060044733448710"),
        ts: 1591900829930000,
        data: {
          fullName: "kim possible"
        }
      },
      locationrefs: [Ref(Collection("Location"), "268060044738691590")],
      locations: [
        {
          locationName: "some place"
        }
      ]
    },
    {
      user: {
        ref: Ref(Collection("User"), "268062175773327877"),
        ts: 1591902862260000,
        data: {
          fullName: "kim asdasd"
        }
      },
      locationrefs: [Ref(Collection("Location"), "268062175777522181")],
      locations: [
        {
          locationName: "some asdasda"
        }
      ]
    },
    {
      user: {
        ref: Ref(Collection("User"), "268062258149458439"),
        ts: 1591902940825000,
        data: {
          fullName: "kim asdasd"
        }
      },
      locationrefs: [
        Ref(Collection("Location"), "268062258156798471"),
        Ref(Collection("Location"), "268062258165187079")
      ],
      locations: [
        {
          locationName: "some asdasda"
        },
        {
          locationName: "some asdasda"
        }
      ]
    },

Final thing we have to do is actually decide whether a location was found. We can use Any for that. Any takes an array of boolean and returns true if any is true. That means we first Map the locations to a boolean on whether they match or not, call Any and we are done. The whole Map query will look like:

Map(
  Paginate(Match(Index('allUsers'))),
  Lambda(
    ['userref'],
    Let(
      {
        user: Get(Var('userref')),
        locationrefs: Select(['data'], Paginate(Match(Index('location_users_by_user'), Var('userref')))),
        locations: Map(Var('locationrefs'), Lambda(['lr'], Select(['data'], Get(Var('lr'))))),
        locationsFound: Any(Map(
          Var('locations'),
          Lambda(['l'], ContainsStr(Select(['locationName'], Var('l')), 'some place'))
        ))
      },
      Var('locationsFound')
    )
  )
)

Now that we are done we transform it to a filter. Note that we moved the Paginate. Filter, in contrary to Map can be applied on the result of Match.

Paginate(Filter(
  Match(Index('allUsers')),
  Lambda(
    ['userref'],
    Let(
      {
        user: Get(Var('userref')),
        locationrefs: Select(['data'], Paginate(Match(Index('location_users_by_user'), Var('userref')))),
        locations: Map(Var('locationrefs'), Lambda(['lr'], Select(['data'], Get(Var('lr'))))),
        locationsFound: Any(Map(
          Var('locations'),
          Lambda(['l'], ContainsStr(Select(['locationName'], Var('l')), 'some place'))
        ))
      },
      Var('locationsFound')
    )
  )
))

Which returns me what I need, the references of the users who have a specific location.

{
  data: [
    Ref(Collection("User"), "268060044733448710"),
    Ref(Collection("User"), "268062263133340167")
  ]
}

If you need the user documents, you’ll map over these and call Get, but you’ve already seen how to do that above :slight_smile:

2 Likes

@databrecht Thank you so much for the explanation. This is very helpful. However, I’m running into an issue where I’m getting back Can't covert data ___ to Vector

I noticed this was previously a bug patched in one of the releases
Fixed can’t convert {data: []} to Vector.

So based off your example, I wrote the following to map over the filtered results to get the User documents

Map(
  Paginate(
    Filter(
      Match(Index("allUsers")),
      Lambda(
        ["userref"],
        Let(
          {
            user: Get(Var("userref")),
            locationrefs: Select(
              ["data"],
              Paginate(Match(Index("location_users_by_user"), Var("userref")))
            ),
            locations: Map(
              Var("locationrefs"),
              Lambda(["lr"], Select(["data"], Get(Var("lr"))))
            ),
            locationsFound: Any(
              Map(
                Var("locations"),
                Lambda(
                  ["l"],
                  ContainsStr(
                    Select(["locationName"], Var("l")),
                    "some place"
                  )
                )
              )
            )
          },
          Var("locationsFound")
        )
      )
    )
  ),
  Lambda(["usersFound"], Get(Var("usersFound")))
)

which yielded the expected result in the FQL editor

{
  data: [
    {
      ref: Ref(Collection("User"), "264519057307337235"),
      ts: 1589068363605000,
      data: {
        hasLoggedIn: true,
        email: "joeblack@example.com",
        fullName: "joe black",
        role: "OWNER",
        customer: Ref(Collection("Customer"), "264519323711701523"),
        phoneNumber: "555-322-8989"
      }
    },
    {
      ref: Ref(Collection("User"), "265696770801009171"),
      ts: 1589647036340000,
      data: {
        hasLoggedIn: false,
        phoneNumber: "555-455-8989",
        email: "johndenver@example.com",
        role: "CLIENT",
        fullName: "John Denver",
        customer: Ref(Collection("Customer"), "265696770670985747"),
        authId: "33415a6b-d5a1-4f36-9218-d8c00d329679"
      }
    },
    {
      ref: Ref(Collection("User"), "267528153337954816"),
      ts: 1591393578810000,
      data: {
        hasLoggedIn: false,
        updatedAt: Date("2020-06-05"),
        phoneNumber: "777-909-3232",
        email: "beauCito4@example.com",
        role: "CLIENT",
        fullName: "Beau Cito4",
        customer: Ref(Collection("Customer"), "267528152656380416"),
        authId: "21c1854b-8336-40d7-b4ad-acbd0675f826",
        createdAt: Date("2020-06-05")
      }
    }
  ]
}

Then I created the following UDF

CreateFunction({
  name: "all_users_by_location3",
  body: Query(
    Lambda(
      ["input"],
      Map(
        Paginate(
          Filter(
            Match(Index("allUsers")),
            Lambda(
              ["userref"],
              Let(
                {
                  user: Get(Var("userref")),
                  locationrefs: Select(
                    ["data"],
                    Paginate(
                      Match(Index("location_users_by_user"), Var("userref"))
                    )
                  ),
                  locations: Map(
                    Var("locationrefs"),
                    Lambda(["lr"], Select(["data"], Get(Var("lr"))))
                  ),
                  locationsFound: Any(
                    Map(
                      Var("locations"),
                      Lambda(
                        ["l"],
                        ContainsStr(
                          Select(["locationName"], Var("l")),
                          Select("name", Var("input"))
                        )
                      )
                    )
                  )
                },
                Var("locationsFound")
              )
            )
          )
        ),
        Lambda(["usersFound"], Get(Var("usersFound")))
      )
    )
  )
}

which I then mapped to the following resolver in my Schema

input LocationInput {
  name: String!
}

type Query {
  allUsers: [User!]! @resolver(name: "all_users", paginated: true)
  allUsersByLocation(input: LocationInput!): [User!]!
    @resolver(name: "all_users_by_location3")
}

Then when calling the following resolver, you’ll notice that it’s actually returning data in the error message but never the less it’s failing to convert to Vector (which I’m curious what that evens means)

I’m less up to date on how data from GraphQL i converted though. I’m assuming it doesn’t like the structure of your return values (it has to be very specific for GraphQL to accept it).
@lregnier, could you chime in on what the error message means?

I think you need to change your graphql query to request data{}. Also if you’re returning the full document like this then you have to pass paginated: true in your @resolver the same way you are for allUsers. If you look at the schema in Graphql Playground after you upload, passing paginated: true actually rewrites your schema definition to return an automatically generated type that includes the ref and ts and then your defined type will exist in data which is why you have to use data in your graphql query when returning the full document

input LocationInput {
  name: String!
}

type Query {
  allUsers: [User!]! @resolver(name: "all_users", paginated: true)
  allUsersByLocation(input: LocationInput!): [User!]!
    @resolver(name: "all_users_by_location3", paginated: true)
}
query {
  allUsersByLocation(input: { name: 'some place' }) {
    data {
      fullName
      createdAt
    }
  }
}
1 Like

You could also rewrite the final part of your UDF to return only the data and then leave the rest as is and it should also work since now you’re truly returning a User type

Lambda(["usersFound"], Select(["data"], Get(Var("usersFound"))))

Hi, @monsieurBoutte! Thank you for joining the Fauna community!

As @yellowspaceboots mentioned, it looks like you should define your query as paginated in your schema, since the UDF seems to be returning a Page.

Also, going back to your original use case:

I’m trying to query for all users that belong to a specific Location via the locationName

I think you could leverage the relations between the two types for doing so!

Try by importing the following schema (note that I’ve added a new Query for getting a Location by its locationName):

type User {
  locations: [Location!]! @relation
  fullName: String
  createdAt: Date
  updatedAt: Date
}

type Location {
  locationName: String! @unique
  users: [User!]! @relation
  createdAt: Date
  updatedAt: Date
}

type Query {
  findLocationByName(locationName: String!) Location
}

And then, run a query as below:

query FindUserByLocationName($locationName: String!) {
  findLocationByName(locationName: $locationName) {
    users {
      data {
        _id
        fullName
      }
    }
  }
}

In case locationName should not be unique, you could try the following as well:

type User {
  locations: [Location!]! @relation
  fullName: String
  createdAt: Date
  updatedAt: Date
}

type Location {
  locationName: String!
  users: [User!]! @relation
  createdAt: Date
  updatedAt: Date
}

type Query {
  findLocationsByName(locationName: String!): [Location]
}

And then:

query FindLocationsByName($locationName: String!) {
  findLocationsByName(locationName: $locationName) {
    data {
      users {
        data {
          _id
          fullName
        }
      }
    }
  }
}

This way you might spare the custom mutation and UDF!

Let me know if this was what you were looking for.

Thanks!

1 Like

Thank you gents for the feedback. Both answers helped but @lregnier you dramatically simplified the solution for me by leveraging the schema that was already in place. Thank you so much. It didn’t even cross my mind before you mentioned it :slight_smile: Also, I’m happy I went the route I did first because it taught me more about how to compose fauna apis

1 Like