Just wanted to know how to put where condition on reference result

[Ankit_Arora]

I have a collection name user and has few fields like name, email, status and a reference for address
Another collection is user_address and has fields like state, city and few other fields

I have created an index named get_user_by_email_and_status which filters if email matches from user collection and has status value active so 1 (here 1 which means active and 0 means inactive)

I am also getting user address in same result set using reference from address with all details like state, city, zip. Now I want to put one more condition if state is ‘NY’ only then user data should be returned. So how I can put this kind of condition on resultset.

My purpose is user result set should return blank as user collection condition satisfies but address which is reference for user collection that condition in case does not satisfies.

Thanks in advance.

[Ankit_Arora]

I have figured out a way to place check on state field which is relational ref field for User.

I have tried this without using Index But now I have another problem.

Paginate(
  Filter(
    Documents(Collection("user")),
    Lambda(
      "x",
      And(
        Equals(
          Select(
            ["data", "state"],
            Get(Select(["data", "address"], Get(Var("x"))))
          ),
          'NY'
        )
      )
    )
  )
)

But the result returns only Refs I want whole result set. If I don’t place AND condition then complete result is returned

Below is the result I am getting now.

{
  data: [
    Ref(Collection("user"), "366507987805667536"),
    Ref(Collection("user"), "366508015583494352")
  ]
}

[ptpaterson]

Hi @Ankit_Arora and welcome!

Filter does not change the output, so the results look like the output of Paginate(Documents(Collection("user")))

To transform the results, use Map

Map(
  Paginate(Filter(...)),
  Lambda(
    "ref",
    Get(Var("ref"))
  )
)

[ptpaterson]

Also, FQL v10 is now in public beta! Try the new dashboard or view our blog and docs to learn more.

Your original query could look something like this with the latest API

user.all()
  .where(.address.state == "NY")

That simple! FQL v10 automatically resolves reference fields and returns the documents from indexes; unlike v4, where paginating the Index will only return the Index’s values