How do I query all the documents created by a particular user?

[lpatmo]

Hi! Beginner question here. I want to write a new function that only returns the todos authored by a specific author. An example of one of my documents looks like:

{
  "ref": Ref(Collection("todos"), "2683675425588801549"),
  "ts": 1592194937000000,
  "data": {
    "description": "some description",
    "url": "https://www.faunadb.com",
    "location": "Everywhere",
    "tags": "fql",
    "completed": false,
    "author": "auth0|5ee2f6049b240a0013247655"
  }
}

I’m following the official Netlify-FaunaDB tutorial, so I see that an example of querying ALL of the todos in the todos-read-all.js file looks like:

client
    .query(q.Paginate(q.Match(q.Ref("indexes/all_todos"))))
    .then((response) => {

… and that indexes/all_todos comes from the boostrap-fauna-database.js file here:

  return client.query(q.Create(q.Ref('classes'), { name: 'todos' }))
    .then(() => {
      return client.query(
        q.Create(q.Ref('indexes'), {
          name: 'all_todos',
          source: q.Ref('classes/todos')
        }))
    }).catch((e) => {
      // Database already exists
      if (e.requestResult.statusCode === 400 && e.message === 'instance not unique') {
        console.log('Fauna already setup! Good to go')
        console.log('Claim your fauna database with "netlify addons:auth fauna"')
        throw e
      }
    })
}

In order to get only the TODOs created by a specific author, this is what I tried:

//todos-read-all-profile.js

/* Import faunaDB sdk */
const faunadb = require("faunadb");
const getId = require("./utils/getId");
const q = faunadb.query;

exports.handler = (event, context) => {
  console.log("Function `todo-read-all-profile` invoked");
  /* configure faunaDB Client with our secret */
  const client = new faunadb.Client({
    secret: process.env.FAUNADB_SERVER_SECRET,
  });
  const userId = getId(event.path);
  return client
    .query(q.Paginate(q.Equals(data.author, userId)))
    .then((response) => {

and

//api.js

const readProfile = (userId) => {
  return fetch(`/.netlify/functions/todos-read-all-profile/${userId}`, {
    method: "GET",
  }).then((response) => {
    return response.json();
  });
};

But the problem, of course, is that .query(q.Paginate(q.Equals(data.author, userId))) I was trying in that todos-read-all-profile.js file doesn’t work.

There is no reference to data, so data is undefined. Does anyone know how I can actually “get” at the data object in the document?

That is, I want to filter all the documents to return ONLY the ones where data. author === userId.

Thanks in advance for any help!

[lpatmo]

Another thought: I’m reading that I should create an index if I want to fetch all the documents that belong to a specific user.

I have an example of that here: https://github.com/netlify/netlify-faunadb-example/blob/master/scripts/bootstrap-fauna-database.js

But I’m confused about how to write a new index that filters all the documents to be only the ones where data.author is equal to the userId passed to the param in:

const readProfile = (userId) => {
  return fetch(`/.netlify/functions/todos-read-all-profile/${userId}`, {
    method: "GET",
  }).then((response) => {
    return response.json();
  });
};

That is, how do I get the userId into that bootstrap-fauna-database.js file?

Elsewhere in todos-read-all-profile.js, I was able to get at it with const userId = getId(event.path);.

[ben]

Search with indexes

I see you have also already asked the same question on slack and gotten an in-depth reply there. Please consider just asking in one place or the other so as to not duplicate effort in answering the same question for the same person twice.

[lpatmo]

@ben Ohh, thank you for letting me know about the reply on Slack! I actually wrote up this post first, then asked in Slack, because I wasn’t sure how active the forum was . I’ll write up the answer that I got here!

[lpatmo]

SOLUTION UPDATE:

I found https://docs.fauna.com/fauna/current/start/fql_for_sql_users.html#select-where to be very helpful in helping me look up the FQL equivalent of SELECT WHERE; in those docs, it is described as:

Map(
  Paginate(
    Match(Index("dept_by_deptno"), 10)
  ),
  Lambda("X", Get(Var("X")))
)

And I learned that I needed to create an index first.

But the real solution came from @Neil on the FaunaDB Slack, who suggested that my index should look like this in FQL:

{
  name: "todo_by_author",
  unique: false,
  serialized: true,
  source: "todos",
  terms: [
    {
      field: ["data", "author"]
    }
  ]
}

… AND that I could create this index directly on the FaunaDB dashboard!

In the UI interface made the mistake initially of populating both ‘data’ and ‘author’ as the terms instead of ‘data.author’, which gave me FQL that looked like:

… but my code started working after I realized my mistake there.

Big thanks to Neil for also suggesting the query that I needed, which was:

.query(q.Paginate(q.Match(q.Index(`todo_by_author`), "auth0|5ee2f6247655" )))

[pier]

FYI @lpatmo instead of doing this:

@lpatmo

"author": "auth0|5ee2f6049b240a0013247655"

You could also store a reference to the author’s document:

"author": Ref(Collection("Authors"), "123456789")

Not sure if it works for your use case but I thought it would be a good idea to mention it.